How to MAP data based on Condition in POWER QUERY

[irfan_abdrhman]

Hello community,

I have an issue for this power BI where I want the SSI Item + SSI Type/Grade ( as a pair ) to be mapped with ST Item + ST Type/Grade (as a pair) where if they currently share the same Yard Location which is why they are in the same row of items, but if they are not mapped together, different values either item is different or Type/Grade is different then the pair ST Item + ST Type/Grade is pushed to the next row along with Yard Location and it's value ST Quantity. 

If ST (Item and Type/Grade) is unable to map with existing SSI (Item and Type/Grade) then will create a new row where it has the, common identifier : Yard Location and Form ID. Then once it is pushed to the next row, the initial row will contain the same common identifier and SSI items. View Excel Table 2 for the expected outcome, and Source for the source data for the whole power query.

Click Here for the Power BI file and also the excel file. The Source, is the from the raw source and I want to generate Main Table (Optional), Table 1 (Created as shown in power bi), Table 2 (unsure how to do the mapping), and Table 3 (Already Created). In the Power BI, the Queries "Testing" is tests I made to try and get Table 2 but failed. Please Help, solve this issue. 

[v-junyant-msft · Accepted solution]

Hi @irfan_abdrhman ,

I saw that you said you had already created Table1 and Table3, so I just copied those two tables as the data source to simplify the steps.😄
For Table1 (if Table1 is otherwise useful to you, please copy the table, I converted Table1 directly in my test):

For Table3 (if Table3 is otherwise useful to you, please copy the table, I've converted Table3 directly in my tests):

Then merge Table1 and Table3 as Table2:

Expand columns:

Use this M function to add a custom column:

if [Quantity] <> null and [Stock Take] <> null then [Quantity] - [Stock Take] else if [Quantity] <> null and [Stock Take] = null then [Quantity] else if [Quantity] = null and [Stock Take] <> null then (0 - [Stock Take]) else null

And the final output is as below (The results are different from the expected results you provided in terms of the order of the rows, so please make some adjustments yourself, but the results are correct!):

Here is the whole M function in advanced editor of the three tables:
Table1:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pdLbCoMgGADgV4l228BzdemWa0JFWA1G9P6vsWIquhnSduXvxcd/nOf0RGiZZumTqyrhazBMfd9IoRK4fqZzpbjs1ogB0LbriwFIl+wwo78xFGPIYxBpB4GFDBh4cSH2YPlVJkOGQZeRYJluurCjwXSuw8Yh17GoI8Zh1xXROqlx1/01BOfJDKz2FxEqNDdOHBtoYdztc6ANH4WSo3iHmllnD6b2GoQxmNuDuXtw67CVnezqpJqGTbX69TT8S9vrIZ7edlsr/hCNG2i5vAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Form ID" = _t, #"Yard Location" = _t, #"Supplier Name" = _t, Material = _t, #"Type/Grade" = _t, Quantity = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Form ID", type text}, {"Yard Location", type text}, {"Supplier Name", type text}, {"Material", type text}, {"Type/Grade", type text}, {"Quantity", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Yard Location", "Material", "Type/Grade"}, {{"Quantity", each List.Sum([Quantity]), type nullable number}})
in
    #"Grouped Rows"

Table3:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lc/NDoMgDAfwVzF41aRFi/GIY1k4wMGPw2J8/9dYYWUHk8V4oW3Ij3/Zd1X3NKpGve3sKsvN1rrZ+sidAQiB6+LXZ75CAD5rBAJ1NLeo/lG8QyduSKS5CkV9TiUosUYLNlBwejv46OOrctuypklq9i75b3YHSMIHXXj/f+tH/rBIvJDjWXZF8srHBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Form ID" = _t, #"Yard Location" = _t, Material = _t, #"Type/Grade" = _t, #"Stock Take Location" = _t, #"Stock Take" = _t, #"MRDO No" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Form ID", type text}, {"Yard Location", type text}, {"Material", type text}, {"Type/Grade", type text}, {"Stock Take Location", type text}, {"Stock Take", Int64.Type}, {"MRDO No", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Yard Location", "Material", "Type/Grade"}, {{"Stock Take", each List.Sum([Stock Take]), type nullable number}})
in
    #"Grouped Rows"

Table2:

let
    Source = Table.NestedJoin(Table1, {"Yard Location", "Material", "Type/Grade"}, Table3, {"Yard Location", "Material", "Type/Grade"}, "Table3", JoinKind.FullOuter),
    #"Expanded Table3" = Table.ExpandTableColumn(Source, "Table3", {"Yard Location", "Material", "Type/Grade", "Stock Take"}, {"Yard Location.1", "Material.1", "Type/Grade.1", "Stock Take"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table3", "Custom", each if [Quantity] <> null and [Stock Take] <> null then [Quantity] - [Stock Take] else if [Quantity] <> null and [Stock Take] = null then [Quantity] else if [Quantity] = null and [Stock Take] <> null then (0 - [Stock Take]) else null)
in
    #"Added Custom"

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-junyant-msft · Accepted solution]

Hi @irfan_abdrhman ,

I saw that you said you had already created Table1 and Table3, so I just copied those two tables as the data source to simplify the steps.😄
For Table1 (if Table1 is otherwise useful to you, please copy the table, I converted Table1 directly in my test):

For Table3 (if Table3 is otherwise useful to you, please copy the table, I've converted Table3 directly in my tests):

Then merge Table1 and Table3 as Table2:

Expand columns:

Use this M function to add a custom column:

if [Quantity] <> null and [Stock Take] <> null then [Quantity] - [Stock Take] else if [Quantity] <> null and [Stock Take] = null then [Quantity] else if [Quantity] = null and [Stock Take] <> null then (0 - [Stock Take]) else null

And the final output is as below (The results are different from the expected results you provided in terms of the order of the rows, so please make some adjustments yourself, but the results are correct!):

Here is the whole M function in advanced editor of the three tables:
Table1:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pdLbCoMgGADgV4l228BzdemWa0JFWA1G9P6vsWIquhnSduXvxcd/nOf0RGiZZumTqyrhazBMfd9IoRK4fqZzpbjs1ogB0LbriwFIl+wwo78xFGPIYxBpB4GFDBh4cSH2YPlVJkOGQZeRYJluurCjwXSuw8Yh17GoI8Zh1xXROqlx1/01BOfJDKz2FxEqNDdOHBtoYdztc6ANH4WSo3iHmllnD6b2GoQxmNuDuXtw67CVnezqpJqGTbX69TT8S9vrIZ7edlsr/hCNG2i5vAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Form ID" = _t, #"Yard Location" = _t, #"Supplier Name" = _t, Material = _t, #"Type/Grade" = _t, Quantity = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Form ID", type text}, {"Yard Location", type text}, {"Supplier Name", type text}, {"Material", type text}, {"Type/Grade", type text}, {"Quantity", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Yard Location", "Material", "Type/Grade"}, {{"Quantity", each List.Sum([Quantity]), type nullable number}})
in
    #"Grouped Rows"

Table3:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lc/NDoMgDAfwVzF41aRFi/GIY1k4wMGPw2J8/9dYYWUHk8V4oW3Ij3/Zd1X3NKpGve3sKsvN1rrZ+sidAQiB6+LXZ75CAD5rBAJ1NLeo/lG8QyduSKS5CkV9TiUosUYLNlBwejv46OOrctuypklq9i75b3YHSMIHXXj/f+tH/rBIvJDjWXZF8srHBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Form ID" = _t, #"Yard Location" = _t, Material = _t, #"Type/Grade" = _t, #"Stock Take Location" = _t, #"Stock Take" = _t, #"MRDO No" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Form ID", type text}, {"Yard Location", type text}, {"Material", type text}, {"Type/Grade", type text}, {"Stock Take Location", type text}, {"Stock Take", Int64.Type}, {"MRDO No", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Yard Location", "Material", "Type/Grade"}, {{"Stock Take", each List.Sum([Stock Take]), type nullable number}})
in
    #"Grouped Rows"

Table2:

let
    Source = Table.NestedJoin(Table1, {"Yard Location", "Material", "Type/Grade"}, Table3, {"Yard Location", "Material", "Type/Grade"}, "Table3", JoinKind.FullOuter),
    #"Expanded Table3" = Table.ExpandTableColumn(Source, "Table3", {"Yard Location", "Material", "Type/Grade", "Stock Take"}, {"Yard Location.1", "Material.1", "Type/Grade.1", "Stock Take"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table3", "Custom", each if [Quantity] <> null and [Stock Take] <> null then [Quantity] - [Stock Take] else if [Quantity] <> null and [Stock Take] = null then [Quantity] else if [Quantity] = null and [Stock Take] <> null then (0 - [Stock Take]) else null)
in
    #"Added Custom"

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[irfan_abdrhman]

Is it possible to combine the Yard Location and Yard Location.1, as well as the Item and Type/Grade? so that if the Yard Location is null then Yard Location.1 is added into Yard Location, and excluding the Yard Location.1 column

[v-junyant-msft · Accepted solution]

Hi @irfan_abdrhman ,

Please change the Table2 into this:

let
    Source = Table.NestedJoin(Table1, {"Yard Location", "Material", "Type/Grade"}, Table3, {"Yard Location", "Material", "Type/Grade"}, "Table3", JoinKind.FullOuter),
    #"Expanded Table3" = Table.ExpandTableColumn(Source, "Table3", {"Yard Location", "Material", "Type/Grade", "Stock Take"}, {"Yard Location.1", "Material.1", "Type/Grade.1", "Stock Take"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table3", "Custom", each if [Quantity] <> null and [Stock Take] <> null then [Quantity] - [Stock Take] else if [Quantity] <> null and [Stock Take] = null then [Quantity] else if [Quantity] = null and [Stock Take] <> null then (0 - [Stock Take]) else null),
    #"Merged Columns" = Table.CombineColumns(#"Added Custom",{"Yard Location", "Material", "Type/Grade", "Yard Location.1", "Material.1", "Type/Grade.1"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    REMOVE = Table.TransformColumns(#"Merged Columns", {{"Merged", each Text.TrimStart(_, ",")}}),
    #"Split Column by Delimiter" = Table.SplitColumn(REMOVE, "Merged", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Merged.1", "Merged.2", "Merged.3", "Merged.4", "Merged.5", "Merged.6"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", type text}, {"Merged.2", type text}, {"Merged.3", type text}, {"Merged.4", type text}, {"Merged.5", type text}, {"Merged.6", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Merged.1", "Yard Location"}, {"Merged.2", "Material"}, {"Merged.3", "Type/Grade"}, {"Merged.4", "Yard Location.1"}, {"Merged.5", "Material.1"}, {"Merged.6", "Type/Grade.1"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","",null,Replacer.ReplaceValue,{"Quantity", "Yard Location.1", "Material.1", "Type/Grade.1"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Replaced Value",{"Yard Location", "Material", "Type/Grade", "Yard Location.1", "Material.1", "Type/Grade.1", "Quantity", "Stock Take", "Custom"})
in
    #"Reordered Columns"

And the final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.