Concatenate list of column names and fill values up in Power Query

Question

Have the following M code where I'm changing 0 values to null (thanks to another user for the help) but am now wanting to fill up because every row has inconsistent data and I want all of the data to be on the top two rows.

I'm now attempting to create a list of column names and stuff it in to Table.FillUp but am getting an expression error that lists the columns and the syntax ("ColumnXXX",) syntax but reads that the columns "of the table" weren't found.

Sample image below of what I"m trying to accomplish. Ideally, all values would move to the first row and null values would be removed.

    Custom1 = Table.ColumnNames(#"Transposed Table2"),
Custom2 = List.Transform(Custom1, each {_, each if _ = 0 then null else _}),
Custom3 = Table.TransformColumns(#"Transposed Table2",Custom2),
Custom4 = """"&Text.Combine(Table.ColumnNames(Custom3),""", """)&"""",
Custom5 = Table.FillUp(Custom3,{Custom4})

Fedor · Accepted Answer · 2023-08-22 16:14:57Z

1

Figured out a better way. Rather than filling up, remove the null values and shift the data up. Solution below.

Custom1 = Table.ColumnNames(#"Transposed Table2"),
Custom2 = List.Transform(Custom1, each {_, each if _ = 0 then null else _}),
Custom3 = Table.TransformColumns(#"Transposed Table2",Custom2),
#"Added Custom" = Table.FromColumns(List.Transform(Table.ToColumns(Custom3), List.RemoveNulls),Table.ColumnNames(Custom3))

edited Aug 22, 2023 at 16:14

Fedor

24.7k45 gold badges59 silver badges188 bronze badges

answered Aug 16, 2023 at 13:27

Sam R.

636 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Concatenate list of column names and fill values up in Power Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related