Table join along with a date comparison. conditional calculation

[ashishd]

Hello friends,

I am not able to find an efficient and correct way to implement the below transformation steps in PowerBI.

The first step Source itself is not returning for a long time and I know the reason. The join should happen on the SKU but also consider that the Date in Order should be within 30 days of the Date in Inventory. But as it can be seen in the Source step I can only define join which is nearly as bad as a cross-join and hence the volume is too much to return quickly.

In SQL, the 2 conditions could have been written together and processed by the database efficiently. But being a newbie in PowerBI, I am lost. Kindly help with your suggestions and inputs.

Thank you!

let
    Source = Table.NestedJoin(#"Sellbrite Inventory", {"SKU"}, #"Sellbrite Order Data", {"SKU"}, "Sellbrite Order Data", JoinKind.LeftOuter),
    #"Expanded Sellbrite Order Data" = Table.ExpandTableColumn(Source, "Sellbrite Order Data", {"Order Date", "SKU", "LineItem Name", "Quantity"}, {"Sellbrite Order Data.Order Date", "Sellbrite Order Data.SKU", "Sellbrite Order Data.LineItem Name", "Sellbrite Order Data.Quantity"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Sellbrite Order Data",{{"Date", Order.Ascending}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Sellbrite Order Data.Order Date", type date}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"Sellbrite Order Data.Quantity"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Last 30 days check", each if [Sellbrite Order Data.Order Date] is null then false
else if ([Sellbrite Order Data.Order Date] <= [Date])
and ([Sellbrite Order Data.Order Date] >= 
Date.AddDays([Date], -30)) then true 
else false),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Last 30 days Order", each if [Sellbrite Order Data.Order Date] is null then 0
else if ([Sellbrite Order Data.Order Date] <= [Date])
and ([Sellbrite Order Data.Order Date] >= 
Date.AddDays([Date], -30)) then 
[Sellbrite Order Data.Quantity] else 0),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Last 30 days Order", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"SKU", "Date", "Inventory Quantity"}, {{"Last 30 days Order", each List.Sum([Last 30 days Order]), type nullable number}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"SKU", "Date", "Last 30 days Order"}, {{"Inventory Quantity", each List.Sum([Inventory Quantity]), type nullable number}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows1", "Channel", each "sellbrite")
in
    #"Added Custom2"

[lbendlin]

  #"Sorted Rows" = Table.Sort(#"Expanded Sellbrite Order Data",{{"Date", Order.Ascending}}),

what do you need that step for?

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[ashishd]

Hi @lbendlin 

I have removed that sorting step as it might have been added when developing and testing the transformations. But removing that step does not help right now as the Source step itself is never getting back with data.

The data source is Sharepoint folder. Two tables based on different criterias are created first in PowerBI and they are fine with now issues. The above presented code is for the 3rd table which is basically a join between the first 2 tables on SKU. But the join should also be accompanied by the date between condition.

Now with small sample data files, this code was running fine but with larger number of files and data, the first step is equivalent to a cross-join as the Date between condition cannot be included together with the join.

So, I want to know how to better process this type of join with a condition in PowerBI.

Thank you!

[lbendlin]

You can only do conditional joins in Power Query. In Power BI you would need to try if TREATAS on disconnected tables is any faster.