Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply

How to MAP data based on Condition in POWER QUERY

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.

irfan_abdrhman_0-1715851743162.png

 

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. 

2 ACCEPTED SOLUTIONS
v-junyant-msft
Community Support
Community Support

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):

vjunyantmsft_0-1715928923105.png

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

vjunyantmsft_1-1715928993960.png

Then merge Table1 and Table3 as Table2:

vjunyantmsft_2-1715929065070.png

Expand columns:

vjunyantmsft_0-1715929526644.png

 

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!):

vjunyantmsft_1-1715929545933.png

 

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.

View solution in original post

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:

vjunyantmsft_0-1716166905142.png


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.

View solution in original post

3 REPLIES 3
v-junyant-msft
Community Support
Community Support

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):

vjunyantmsft_0-1715928923105.png

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

vjunyantmsft_1-1715928993960.png

Then merge Table1 and Table3 as Table2:

vjunyantmsft_2-1715929065070.png

Expand columns:

vjunyantmsft_0-1715929526644.png

 

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!):

vjunyantmsft_1-1715929545933.png

 

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.

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

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:

vjunyantmsft_0-1716166905142.png


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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors