Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Max1
Regular Visitor

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:

table1.png

  • “Key” column is always fixed
  • Total number of other columns will change dynamically (but always a minimum of 3 columns)
  • Every row will always have only three values in total: “key” and two more distinct values

 

Output table should look like this

My task is to obtain a table where every row will have “key” value and two non-NULL values.

table2.png

 

1 ACCEPTED SOLUTION

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
Specializing in Power Query Formula Language (M)

View solution in original post

7 REPLIES 7
v-yulgu-msft
Employee
Employee

Hi @Max1,

 

The summarized steps could be:

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

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

  3. Pivot table: Select the new rank column, then, click the pivot column option.
    4.PNG
  4. Result
    3.PNG
    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

 

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yulgu-msft,

Thanks a lot for your solution! 

It worked fine for me

 

Have a nice day!

Max

MarcelBeug
Community Champion
Community Champion

@Max1 Why are you going for the long solution and not for the short one?

Specializing in Power Query Formula Language (M)

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
Specializing in Power Query Formula Language (M)

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

mechanix85
Helper I
Helper I

Hi,

 

Таблица1=

 

Безымянный.png

let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Другие столбцы с отмененным свертыванием" = Table.UnpivotOtherColumns(Источник, {"key"}, "Атрибут", "Значение"),
    #"Измененный тип" = Table.TransformColumnTypes(#"Другие столбцы с отмененным свертыванием",{{"Значение", type text}}),
    #"Сгруппированные строки" = Table.Group(#"Измененный тип", {"key"}, {{"Количество", each _, type table}}),
    #"Добавлен пользовательский объект4" = Table.AddColumn(#"Сгруппированные строки", "Custom", each Table.ToList(Table.Transpose(Table.FromList(Table.Column([Количество],"Значение"))),Combiner.CombineTextByDelimiter("|"))),
    #"Развернуть Custom.4" = Table.ExpandListColumn(#"Добавлен пользовательский объект4", "Custom"),
    #"Разделить столбец разделителем" = Table.SplitColumn(#"Развернуть Custom.4","Custom",Splitter.SplitTextByDelimiter("|"),{"Custom.1", "Custom.2", "Custom.3"}),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Разделить столбец разделителем",{"Количество"})
in
    #"Удаленные столбцы"

result=

 

Безымянный2.png

 

 

 

 

 

mechanix85
Helper I
Helper I

Hi

"Таблица1" =

 

 

 

Безымянный.png

let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Другие столбцы с отмененным свертыванием" = Table.UnpivotOtherColumns(Источник, {"key"}, "Атрибут", "Значение"),
    #"Измененный тип" = Table.TransformColumnTypes(#"Другие столбцы с отмененным свертыванием",{{"Значение", type text}}),
    #"Сгруппированные строки" = Table.Group(#"Измененный тип", {"key"}, {{"Количество", each _, type table}}),
    #"Добавлен пользовательский объект4" = Table.AddColumn(#"Сгруппированные строки", "Custom", each Table.ToList(Table.Transpose(Table.FromList(Table.Column([Количество],"Значение"))),Combiner.CombineTextByDelimiter("|"))),
    #"Развернуть Custom.4" = Table.ExpandListColumn(#"Добавлен пользовательский объект4", "Custom"),
    #"Разделить столбец разделителем" = Table.SplitColumn(#"Развернуть Custom.4","Custom",Splitter.SplitTextByDelimiter("|"),{"Custom.1", "Custom.2", "Custom.3"}),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Разделить столбец разделителем",{"Количество"})
in
    #"Удаленные столбцы"

result=

 

Безымянный2.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.