Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

M Script Help - custom column referencing another table

I have the following situation that I would like to fix with power query. I have a sales fact table and a department dimension. In the sales table, I have a seller column, where each seller belongs t...
Need Help
  • ImkeF's avatar
    ImkeF
    8 years ago

    You can add a column in sales with this formula: 

     

    Table.SelectRows(Department, 
        		(Dept) =>  (Dept[FromDate] < [Invoice Date]) and
           		(Dept[ToDate]  >=  [Invoice Date]) and 
           		(Dept[Seller] = [Seller])
            	)[Department]{0}

     

    This will lookup the desired value from te Department-table (and if it is null, replace it with the value of your current row).

    But it might be slow. To speed it up you can "partition" your Sales table by grouping it on Seller, merge with Department on seller and apply the above selection-formula in the partitioned fields: Just omit the "(Dept[Seller] = [Seller])"-part then.

     

    Another alternative is to create an intermediate table that you dont load to the data model where you expand the dates of the time intervals of your Department-table so that every day will have one row. Then you can simply merge that new date-column with your Sales-table.

ImkeF's avatar
ImkeF
Community Champion
8 years ago

You can add a column in sales with this formula: 

 

Table.SelectRows(Department, 
    		(Dept) =>  (Dept[FromDate] < [Invoice Date]) and
       		(Dept[ToDate]  >=  [Invoice Date]) and 
       		(Dept[Seller] = [Seller])
        	)[Department]{0}

 

This will lookup the desired value from te Department-table (and if it is null, replace it with the value of your current row).

But it might be slow. To speed it up you can "partition" your Sales table by grouping it on Seller, merge with Department on seller and apply the above selection-formula in the partitioned fields: Just omit the "(Dept[Seller] = [Seller])"-part then.

 

Another alternative is to create an intermediate table that you dont load to the data model where you expand the dates of the time intervals of your Department-table so that every day will have one row. Then you can simply merge that new date-column with your Sales-table.

edhans's avatar
edhans
Community Champion
6 years ago

ImkeF why is this necessary?

ImkeF wrote:

You can add a column in sales with this formula: 

 

Table.SelectRows(Department, 
    		(Dept) =>  (Dept[FromDate] < [Invoice Date]) and
       		(Dept[ToDate]  >=  [Invoice Date]) and 
       		(Dept[Seller] = [Seller])
        	)[Department]{0}

 

This will lookup the desired value from te Department-table (and if it is null, replace it with the value of your current row).

In other words, what exactly is (Dept) => doing here?

This was super helpful for me, but not 100% sure what is going on. I cannot figure out why I cannot simply use something like

=Table.AddColumn(#"Changed Type", "Other", each Table.SelectRows(OtherTable, each [OtherTableColumn] = 1))

I can do this with other steps in the current query, using something like this:

Table.AddColumn(#"Changed Type", "Other", each Table.SelectRows(#"Changed Type", each [SomeColumn] = 1))

But obviously the table I'm referencing in my first example (OtherTable) isn't the same as a table in another step (#"Changed Type").

 

Your code works beautifully, but not tracking it...