Forum Discussion

Max1's avatar
Max1
Regular Visitor
9 years ago
Solved

Extract number values from columns

Hello! Can anybody help me to transform a table where total number of columns is not constant?  Thanks   Input Table: “Key” column is always fixed Total number of other columns will chan...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    Alternatively you can take the second {1} - 0-based - and third {2} non-null field values.

     

    let
    Source = Table1,
    AddedColumn2 = Table.AddColumn(Source, "column2", each List.Select(Record.FieldValues(_), each _<> null){1}),
    AddedColumn3 = Table.AddColumn(AddedColumn2, "column3", each List.Select(Record.FieldValues(_), each _<> null){2}),
    SelectedColumns = Table.SelectColumns(AddedColumn3,{"Key", "column2", "column3"})
in
    SelectedColumns
MarcelBeug's avatar
MarcelBeug
Community Champion
9 years ago

Alternatively you can take the second {1} - 0-based - and third {2} non-null field values.

 

let
    Source = Table1,
    AddedColumn2 = Table.AddColumn(Source, "column2", each List.Select(Record.FieldValues(_), each _<> null){1}),
    AddedColumn3 = Table.AddColumn(AddedColumn2, "column3", each List.Select(Record.FieldValues(_), each _<> null){2}),
    SelectedColumns = Table.SelectColumns(AddedColumn3,{"Key", "column2", "column3"})
in
    SelectedColumns
Max1's avatar
Max1
Regular Visitor
9 years ago

Hi MarcelBeug

 

Thanks a lot for your solution. That's a shorter and easier way.

Works fine for my table.

 

Have a nice day!

Max