Forum Discussion

mckee's avatar
mckee
New Member
4 years ago
Solved

Create column with other column titles

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.   Columns are oldest to newest...
Need Help
  • latimeria's avatar
    latimeria
    4 years ago

    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

     

     

     

     

latimeria's avatar
latimeria
Icon for Solution Specialist rankSolution Specialist
4 years ago

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

 

  • mckee's avatar
    mckee
    New Member
    4 years ago

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