Forum Discussion

kimmal's avatar
kimmal
Regular Visitor
8 years ago
Solved

Help Unpivoting data

I have data which I need to unpivot into 2 columns, one for the week attribute and one for the value.  All the data in on the same row.  The issue is I have multiple columns for data with the associa...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi kimmal,

     

    With help from MarcelBeug, this code will perfectly solve your probelm - there will no longer be a limitation of upto 10 columns

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"WO", type text}, {"Date_Received", type datetime}, {"ProcessDate1", type datetime}, {"Value1", Int64.Type}, {"ProcessDate2", type datetime}, {"Value2", Int64.Type}, {"ProcessDate3", type datetime}, {"Value3", Int64.Type}, {"ProcessDate4", type datetime}, {"Value4", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"WO", "Date_Received"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each Text.Combine(Text.SplitAny([Attribute],"0123456789"))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.End([Attribute],Text.Length([Attribute])-Text.Length([Custom]))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Custom.1"})
in
    #"Removed Columns1"

    Hope this helps.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi MarcelBeug,

 

Thank you for sharing this.  What would be the code for extracting the number into a different column?  The number will be of different length in each row.

MarcelBeug's avatar
MarcelBeug
Community Champion
8 years ago

I posted a solution to get digits from a string in this topic.

 

Basicallly the string is splitted on digits, from the reulting list blank items are removed.

This gives you a list with all non-digit parts in the string.

This can be used as delimiters to split the original string again, using function Splitter.SplitTextByEachDelimiter.

After removing empty entries, you have a list with all the digits-parts which you can combine with Text.Combine.