10

I want to create a new table based on this one: This is my initial table

that filters for Warehouse=2 and "drops" the columns "Price" and "Cost" like this:

This is what I want to have

I have managed to apply the filter in the first step using:

FILTER(oldtable;oldtable[Warehouse]=2)

and then in the next step cold create another table that only selects the required columns using:

newtable2=SELECTCOLUMNS("newtable1";"Articlename";...)

But I want to be able to combine these two functions and create the table straight away.

Lorenz Joe
  • 103
  • 1
  • 1
  • 5

1 Answers1

11

This is very simple, because in your first step, a table is returned which you can use directly in your second statement.

newTabel = SELECTCOLUMNS(FILTER(warehouse;warehouse[Warehouse]=2);"ArticleName";warehouse[Articlename];"AmountSold";warehouse[AmountSold];"WareHouse";warehouse[Warehouse])

If you want to keep the overview, you can also use variables and return:

    newTabel = 
        var filteredTable = FILTER(warehouse;warehouse[Warehouse]=2)
        return SELECTCOLUMNS(filteredTable;"ArticleName";warehouse[Articlename];"AmountSold";warehouse[AmountSold];"WareHouse";warehouse[Warehouse])
Aldert
  • 4,209
  • 1
  • 9
  • 23
  • Hi again, Aldert! Both solutions work great-thank you for that. However, when I apply your solutions, the columns get put in a different order like this Warehouse->ArticleName->Amount sold. But I actually want the order like in the statement so ArticleName->AmoundSold->Warehouse. Do you know how to fix this? – Lorenz Joe Jul 18 '19 at 07:22
  • @LorenzJoe, the order in your tables in not relevant in PowerBI. The reason is that you control the order in your visuals. I also wonder a bit why you need this table for wharehouse 2 only because you can use the visual to filter only on warehouse 2. – Aldert Jul 18 '19 at 07:39
  • Oh okay, makes sense. I actually need to filter the amountSold as there are some errors in the data that have to be cleaned before further data modeling (the answer you gave to my previous question of the inventory). But the same logic applies. Thanks again – Lorenz Joe Jul 18 '19 at 07:51
  • @LorenzJoe, if you have some errors in your data, you should take care of this in mquery. This ensures that for all visuals, you are working with the clean data. – Aldert May 08 '21 at 07:38