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 all
I'm new to PQ and M-Code.
I want to create a new column. It should store the name of other columns, depending on the data inside these other columns.
Thank you for any inputs in advance and feel free to ask back.
Marc
Solved! Go to Solution.
Try with this code. One way to sort out the issue
update: I sligthly change the code to meet your requirements. You are not ooking for the max value.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlXSUTIDYnOlWJ1oJUsgyxiITcA8QwMg0wjENzSCCFiABECiSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"2020/1" = _t, #"2020/2" = _t, #"2020/3" = _t]),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
#"Filtered Rows1" = Table.SelectRows(#"Unpivoted Other Columns", each [Value] <> null and [Value] <> ""),
#"Grouped Rows" = Table.Group(#"Filtered Rows1", {"Index"}, {{"Max", each List.Max([Attribute]), type text}, {"Rows", each _, type table [Index=number, Attribute=text, Value=text]}}),
#"Added Index for columns names" = Table.TransformColumns( #"Grouped Rows", {"Rows", each Table.AddIndexColumn(_, "I")}),
#"Expanded Rows" = Table.ExpandTableColumn(#"Added Index for columns names", "Rows", {"Attribute", "I", "Value"}, {"Attribute", "I", "Value"}),
#"Filtered Rows - hold max" = Table.SelectRows(#"Expanded Rows", each [Max] = [Attribute]),
#"Merged Queries Added Index withfiltered rows" = Table.NestedJoin(#"Added Index", {"Index"}, #"Filtered Rows - hold max", {"Index"}, "Filtered Rows", JoinKind.LeftOuter),
#"Expanded Filtered Rows" = Table.ExpandTableColumn(#"Merged Queries Added Index withfiltered rows", "Filtered Rows", {"I"}, {"I"}),
#"Added Columns Names" = Table.AddColumn(#"Expanded Filtered Rows", "Names", each Table.ColumnNames(Source){[I]}),
Cleanup = Table.RemoveColumns(#"Added Columns Names",{"Index", "I"})
in
Cleanup
That)s right.
Streamline version
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlXSUTIDYnOlWJ1oJUsgyxiITcA8QwMg0wjENzSCCFiABECiSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"2020/1" = _t, #"2020/2" = _t, #"2020/3" = _t]),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
#"Filtered Rows1" = Table.SelectRows(#"Unpivoted Other Columns", each [Value] <> null and [Value] <> ""),
#"Grouped Rows" = Table.Group(#"Filtered Rows1", {"Index"}, {{"Max", each List.Max([Attribute]), type text}}),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each #"Grouped Rows"[Max] {List.PositionOf(#"Grouped Rows"[Index], [Index])}),
Cleanup = Table.RemoveColumns(#"Added Custom",{"Index"})
in
Cleanup
Like I said, I'm a beginner, so I wasn't able to work with the M-Code samples.
But what I could read from it was enough to get the idea, and the I re-created the process via PowerQuery's click-the-GUI buttons 🙂
Thank you for the help!
Now I need to find a way to work with changing column titles in the source file(s)...
Try with this code. One way to sort out the issue
update: I sligthly change the code to meet your requirements. You are not ooking for the max value.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlXSUTIDYnOlWJ1oJUsgyxiITcA8QwMg0wjENzSCCFiABECiSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"2020/1" = _t, #"2020/2" = _t, #"2020/3" = _t]),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
#"Filtered Rows1" = Table.SelectRows(#"Unpivoted Other Columns", each [Value] <> null and [Value] <> ""),
#"Grouped Rows" = Table.Group(#"Filtered Rows1", {"Index"}, {{"Max", each List.Max([Attribute]), type text}, {"Rows", each _, type table [Index=number, Attribute=text, Value=text]}}),
#"Added Index for columns names" = Table.TransformColumns( #"Grouped Rows", {"Rows", each Table.AddIndexColumn(_, "I")}),
#"Expanded Rows" = Table.ExpandTableColumn(#"Added Index for columns names", "Rows", {"Attribute", "I", "Value"}, {"Attribute", "I", "Value"}),
#"Filtered Rows - hold max" = Table.SelectRows(#"Expanded Rows", each [Max] = [Attribute]),
#"Merged Queries Added Index withfiltered rows" = Table.NestedJoin(#"Added Index", {"Index"}, #"Filtered Rows - hold max", {"Index"}, "Filtered Rows", JoinKind.LeftOuter),
#"Expanded Filtered Rows" = Table.ExpandTableColumn(#"Merged Queries Added Index withfiltered rows", "Filtered Rows", {"I"}, {"I"}),
#"Added Columns Names" = Table.AddColumn(#"Expanded Filtered Rows", "Names", each Table.ColumnNames(Source){[I]}),
Cleanup = Table.RemoveColumns(#"Added Columns Names",{"Index", "I"})
in
Cleanup
This is solid logic. I think you can streamline it by dropping your [Rows] column entirely in the Group By though. Your [Max] column should already have the latest month and you can skip straight to the merge.
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.
User | Count |
---|---|
102 | |
48 | |
19 | |
13 | |
11 |