Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi everyone,
I want to ask a help for solving my problem
I have a table like this
I need to replace the null value with these conditional :
If they have same Code 1 and Description, please fill the same Code 2
My expectation table like this
What M Code I should write to get my expectations?
Please help me to solve this problem
Thank you..
Hi @Mars3442 ,
One potential one:
Before:
After:
Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRCijKTylNLlFwBLIdQUKxOtSTooowha4yMTVDknICsp1AQtilSNdBnhSpwrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Code 1" = _t, Description = _t, #"Code 2" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code 1", Int64.Type}, {"Description", type text}, {"Code 2", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Code 1", "Description"}, {{"MaxCode2", each List.Max([Code 2]), type nullable text}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Code 1", "Description"}, #"Grouped Rows", {"Code 1", "Description"}, "Grouped Rows", JoinKind.LeftOuter), #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"MaxCode2"}, {"Grouped Rows.MaxCode2"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Grouped Rows",{"Code 2"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Grouped Rows.MaxCode2", "Code 2"}}) in #"Renamed Columns"
Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |