cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Extract number values from columns

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
Highlighted
Helper I
Helper I

Re: Extract number values from columns

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

 

Highlighted
Helper I
Helper I

Re: Extract number values from columns

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

 

 

 

 

 

Highlighted
Microsoft
Microsoft

Re: Extract number values from columns

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.
Highlighted
Community Champion
Community Champion

Re: Extract number values from columns

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

Regular Visitor

Re: Extract number values from columns

Hi @v-yulgu-msft,

Thanks a lot for your solution! 

It worked fine for me

 

Have a nice day!

Max

Highlighted
Community Champion
Community Champion

Re: Extract number values from columns

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

Specializing in Power Query Formula Language (M)
Highlighted
Regular Visitor

Re: Extract number values from columns

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors
Users online (924)