cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mckee
New Member

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.

 

  1. Columns are oldest to newest (left to right) and contain quantities (currently stored as text, but I can switch to numeric, doesn't matter for my purpose)
  2. I want to identify the newest (=most right) column which contains data (<> null and <> "" and <> 0) for each line
  3. I want to transfer the name of the column found in step 2 into a new column for each line

 

screenshotscreenshot

 

 

 

 

 

Thank you for any inputs in advance and feel free to ask back.
Marc

1 ACCEPTED SOLUTION
latimeria
Resolver I
Resolver I

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_0-1637766957944.png

 

 

View solution in original post

4 REPLIES 4
latimeria
Resolver I
Resolver I

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

latimeria
Resolver I
Resolver I

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_0-1637766957944.png

 

 

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.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!