How to MAP data based on Condition in POWER QUERY

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.

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.