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,

 

You are welcone.  This isn't the best solution die to the 10 column limitation.  If someone can tell us how to extract text from an alphanumeric string in Power Query, then you will get the perfect solution.  So from an alphanumeric string such as ABCD435, how does one extract abcd?  Also, that solution should work for different lengths of the text and numeric portion.

 

I suggest you start another thread with the question of how to extract the text portion of an alphanumeric string where the length of each portion is unknown.  Once we get that solution, we will plug it into my code shared above.

MarcelBeug's avatar
MarcelBeug
Community Champion
8 years ago

How about

 

= Text.Combine(Text.SplitAny("ABCD435","0123456789"))
  • 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.