Help Unpivoting data

[kimmal]

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 associated value next to it.   The 1st 2 rows are what the data currently looks like when I get it.  The bottom 5 rows are what I need it to look like.  I've tried the Unpivot command unsuccessfully as the unpivot doesn't maintain the relationship between the Date field and the assoicated Value field (i.e. ProcessDate1 and Value1 are related).  I'm wondering if either (1) people have any thoughts on how I can do this or (2) tell me where I'm going wrong with the Unpivot command.  

WO	Date_Received	ProcessDate1	Value1	ProcessDate2	Value2	ProcessDate3	Value3	ProcessDate4	Value4
ABC	16-May-17	18-May-17	25	23-May-17	8	26-May-17	1	05-Jun-17	2
WO	WO_Received	Date	Value
2016-0000337	16-May-17	18-May-17	25
2016-0000337	16-May-17	23-May-17	8
2016-0000337	16-May-17	26-May-17	1
2016-0000337	16-May-17	05-Jun-17	2

[v-chuncz-msft]

@kimmal,

By the way, you may also take a look at the GENERATE Function similar to the CROSS APPLY condition in SQL.

[Ashish_Mathur]

Hi,

Try this M language solution

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"),
    #"Split Column by Position" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByPositions({0, 1}, true), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.1]), "Attribute.1", "Value")
in
    #"Pivoted Column"

The limitation of this method is that it will only work with a dataset which has upto 10 columns of ProcessDate per WO.

Hope this helps.

[kandre123]

@Ashish_Mathur how do this code work for 300 columns?

[Ashish_Mathur]

Hi,

Describe the question, share some data and show the expected result.

[kimmal]

Thanks, Ashish, this worked perfectly, except for the fact that it's limited to 10 columns, but I think I can fix the source file to exclude 3 of the columns

[Ashish_Mathur · Accepted solution]

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.

[kimmal]

Thanks Ashish,  this worked perfectly without having to split up the data file into 3 speperate queries and then merging the results back together (which also worked perfectly)

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

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]

How about

= Text.Combine(Text.SplitAny("ABCD435","0123456789"))

[Ashish_Mathur]

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]

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.

[MarcelBeug]

After unpivot, the trick is to add an Index column (starting with 0) and integer-divide that column by the number of different items (i.c. 2). Then you can adjust the attribute column so - in this case - you have "Date" and "Value" entries.

Now you can pivot back (with advanced option "Don't Summarize"), remove the Index column and adjust data types.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"WO", type text}, {"Date_Received", type date}, {"ProcessDate1", type date}, {"Value1", Int64.Type}, {"ProcessDate2", type date}, {"Value2", Int64.Type}, {"ProcessDate3", type date}, {"Value3", Int64.Type}, {"ProcessDate4", type date}, {"Value4", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"WO", "Date_Received"}, "Attribute", "Value"),
    #"Transformed Attribute" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Attribute", each if Text.Contains(_,"Date") then "Date" else "Value"}}),
    #"Added Index" = Table.AddIndexColumn(#"Transformed Attribute", "Index", 0, 1),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 2), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[Attribute]), "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}, {"Value", Int64.Type}})
in
    #"Changed Type1"