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
v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
9 years ago

Hi Max1,

 

The summarized steps could be:

  1. Unpivot table: Select [Key] column, then unpivot other columns
     

  2. Add a nested rank column. Please refer to this blog for details about how to add a nested rank column.


  3. Pivot table: Select the new rank column, then, click the pivot column option.
  4. Result

    let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxxx\Desktop\Sample Data.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Key", type text}, {"bd-2", Int64.Type}, {"cc-2", Int64.Type}, {"cc-1", Int64.Type}, {"bd-1", Int64.Type}, {"cc-3", Int64.Type}, {"bd-3", Int64.Type}, {"cc-5", Int64.Type}, {"bd-5", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Key"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Key"}, {{"New Column1", each _, type table}}),
RankFunction = (tabletorank as table) as table =>
     let
      #"SortRows" = Table.Sort(tabletorank,{{"Value", Order.Descending}}),
      #"AddIndex" = Table.AddIndexColumn(#"SortRows", "Rank", 1, 1)
     in
      #"AddIndex",
    #"Added Index2" = Table.TransformColumns(#"Grouped Rows", {"New Column1", each RankFunction(_)}),
    #"Expanded New Column1" = Table.ExpandTableColumn(#"Added Index2", "New Column1", {"Attribute", "Value", "Rank"}, {"New Column1.Attribute", "New Column1.Value", "New Column1.Rank"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded New Column1",{"New Column1.Attribute"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"New Column1.Rank", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"New Column1.Rank", type text}}, "en-US")[#"New Column1.Rank"]), "New Column1.Rank", "New Column1.Value", List.Sum),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"1", "Column1"}, {"2", "Column2"}})
in
    #"Renamed Columns"

Best regards,
Yuliana Gu

 

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