Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I've been searching around but couldn't find any solution which would solve my problem completely.
Let me explain the issue using the below example table:
Column A | Column B | Column C |
123 | four | one |
456 | five | two |
789 | six | three |
null | seven | four |
I want to replace the null values in Column A by the Column A value of the row where Column B value = Column C value of my null row.
So in the example I want to replace the "null" in the last row by "123" as that value is in the row where Column B (four) = Column C of my null row (four).
It would be great if someone could help me.
Thanks!
Solved! Go to Solution.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRSssvLQJS+XmpSrE60UompmYgwcyyVCBVUp4PFjS3sATyijMrQGIZRakQpSCh1LLUPJghsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t, #"Column C" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", Int64.Type}, {"Column B", type text}, {"Column C", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Column A] <> null then [Column A] else Table.SelectRows(#"Changed Type",(inner)=>inner[Column B]=[Column C])[Column A]{0}),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column A"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "Column B", "Column C"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom", "Column A"}})
in
#"Renamed Columns"
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs
Cheers
Hi @BB1993
Do you need this in DAX or Power Query
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs
Cheers
Hi,
I need it in Power Query.
Thanks!
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRSssvLQJS+XmpSrE60UompmYgwcyyVCBVUp4PFjS3sATyijMrQGIZRakQpSCh1LLUPJghsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t, #"Column C" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", Int64.Type}, {"Column B", type text}, {"Column C", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Column A] <> null then [Column A] else Table.SelectRows(#"Changed Type",(inner)=>inner[Column B]=[Column C])[Column A]{0}),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column A"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "Column B", "Column C"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom", "Column A"}})
in
#"Renamed Columns"
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs
Cheers
Hi @AlB
Thank you, this worked!
Just for my total understanding: Can you explain what the "(inner) => inner[Column B]" means?
Cheers!
Hi @BB1993
Sure. each is syntax sugar for (_)=>
We have two nested operations here
Table.AddColumn(#"Changed Type", "Custom", each if [Column A] <> null then [Column A] else Table.SelectRows(#"Changed Type",(inner)=>inner[Column B]=[Column C]) [Column A]{0} )
If we used "each" on both, when in the inner operation (Table.SelectRows) we wouldn't be able to refer to the variables of the outer operation ([Column C] in this case). So we use another input variable name (inner) instead of "_" in this case. With that we can tell M whether we are referring to the inner or the outer variables. The reasoning is similar to the use of EARLIER() in DAX to refer to the previous row context.
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
What would be DAX formula if one of the columns was in a different table?
Please be more specific. I don't understand what you need
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
How to use two columns if both are in different tables, i posted a question earlier to forum, titled: 2 Tables: Value of column to be blank if the other column value is blank.
I need to have dax look for a blank value from the 1st table column 'STEP Projects' [PM/AppTransitionMtg] to then replace the date value in the 2nd table column 'STEP Resource Planning' [Start] to blank . If no blank is found from the 1st table, then 2nd table column 'STEP Resource Planning' [Start] remains with its original value.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.