Table transformation logic in power bi

[Ramees_123]

In In my business logis sales are happening in 3 phases, first customer order will come in to the inbound after production it moves in to outbound and at last it reached in the completed(Day Sale). How to achieve if it moves from inbound to outboud the sales order and line should not exist in the inbound, if it moves from outbound to complete it should not exist in the outbound, how i can achieve this in power bi transformation table. depending on the sales order and line it is moving throuhg the phases.

[msksenthil]

@Ramees_123 

I believe you have unique fields like customer_id/sales_id and date_field in all the tables

With that, you can merge the tables from the 'Inbound' table to the 'Outbound' table and to the 'Completed' table

And expand table particularly the date from each table

So the final table has all the 3 dates

Disable load the other 3 tables

Create a custom column based on the 3 dates to find the record is Inbound/Outboud/Completed

finally, you can create a measure based on a custom column and find the number of orders

Here the M-code for Merge/final table

let
Source = Table.NestedJoin(Inbound, {"customer order", "saleorder"}, Outbound, {"customer order", "saleorder"}, "Outbound", JoinKind.LeftOuter),
#"Expanded Outbound" = Table.ExpandTableColumn(Source, "Outbound", {"out_createddate"}, {"out_createddate"}),
#"Merged Queries" = Table.NestedJoin(#"Expanded Outbound", {"customer order", "saleorder"}, Completed, {"customer order", "saleorder"}, "Completed", JoinKind.LeftOuter),
#"Expanded Completed" = Table.ExpandTableColumn(#"Merged Queries", "Completed", {"comp_createddate"}, {"comp_createddate"}),
#"Added Custom" = Table.AddColumn(#"Expanded Completed", "Custom", each try if Int64.From([comp_createddate]) > 0 then "Completed" else "no" otherwise try if Int64.From([out_createddate]) > 0 then "Outbound" else "Inbound" otherwise "Inbound")
in
#"Added Custom"

Please share your results

[Icey · Accepted solution]

Hi @Ramees_123 ,

I create a simple .pbix file. Please check if this is what you want.

1. Import your data.

2. Create another 3 tables like so:

let
    Source = #"Inbound",
    #"Filtered Rows" = Table.SelectRows(Source, each ([status] = "Inbound"))
in
    #"Filtered Rows"

let
    Source = #"Inbound",
    OnlyIncludeOutbound = Table.SelectRows(Source, each ([status] = "Outbound")),
    AppendOutbound = Table.Combine({OnlyIncludeOutbound, #"Outbound"}),
    ReturnOutbound = Table.SelectRows(AppendOutbound, each ([status] = "Outbound"))
in
    ReturnOutbound

let
    Source = #"Outbound",
    OnlyIncludeCompleted = Table.SelectRows(Source, each ([status] = "Completed")),
    AppendCompleted = Table.Combine({OnlyIncludeCompleted, #"Completed"}),
    ReturnCompleted = Table.SelectRows(AppendCompleted, each ([status] = "Completed"))
in
    ReturnCompleted

2. Uncheck "Enable load" of each original table.

For more details, please check the attached .pbix file.

Best regards

Icey

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[amitchandak]

@Ramees_123 , Is that not happening in your source system

[Ramees_123]

No, its not happening in our source system, this should implement by using the power bi logic. the shared youtube videos not discussing this logic.