How to unpivot based on multiple attributes

[red_arrowhead]

I have a table where I have data in a form that each type has values from Jan to December and also another Jan to December columns for specific dates in each month for those values. 

Example dataset I have is similar to this:

Now after unpivoting rules, I want this data in the following format

But when I am doing it firstly as I have two types to be unpivoted values and dates, I tried unpivoting the data set twice once for values then for dates, but it is generating some wrong combinations. 

How can I convert this type of dataset into the format I want as, the actual data set I have is very big with values and dates from Jan to december for both. 

How should I solve this?

[AlB · Accepted solution]

@red_arrowhead 

Here you go. I've made it a bit more dynamic so as not to hardcode the names of MonthYear columns. Place the following M code in a blank query to see the steps. The first two steps are just to load the sample data you provided:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7ZWxDoIwFEX/pbMJvQUERlnc3QxxsxvRxMT4+xZUOKyyNKTLbU66nL6b5nWdOZidOflrSFkbVboms8qcdT9whJxQEErCnlARakIDkCXQQAuD8UYD1F830U10E91EN9FNdBPdRDfRTXSTuew+nZ59399fw91nmPFkqnVFrW3/9OHIx1HGk+EBc6kDOEJOKAglYU+oCDWhAcylDkADLQymkU5uopvoJrqJbqKb6Ca6iW6im+g2l3p8eH8LZzHOMp5MX/Xfr9qatFW32GnaqpusNdKtmkpdU2raqptq9fIG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Description = _t, #"Cost Type" = _t, #"Jan 20" = _t, #"Feb 20" = _t, #"Mar 20" = _t, #"Apr 20" = _t, #"May 20" = _t, #"Jun 20" = _t, #"Jul 20" = _t, #"Aug 20" = _t, #"Sep 20" = _t, #"Oct 20" = _t, #"Nov 20" = _t, #"Dec 20" = _t, #"Jan 21" = _t, #"Feb 21" = _t, #"Mar 21" = _t, #"Apr 21" = _t, #"May 21" = _t, #"Jun 21" = _t, #"Jul 21" = _t, #"Aug 21" = _t, #"Sep 21" = _t, #"Oct 21" = _t, #"Nov 21" = _t, #"Dec 21" = _t, #"Jan 20.1" = _t, #"Feb 20.1" = _t, #"Mar 20.1" = _t, #"Apr 20.1" = _t, #"May 20.1" = _t, #"Jun 20.1" = _t, #"Jul 20.1" = _t, #"Aug 20.1" = _t, #"Sep 20.1" = _t, #"Oct 20.1" = _t, #"Nov 20.1" = _t, #"Dec 20.1" = _t, #"Jan 21.1" = _t, #"Feb 21.1" = _t, #"Mar 21.1" = _t, #"Apr 21.1" = _t, #"May 21.1" = _t, #"Jun 21.1" = _t, #"Jul 21.1" = _t, #"Aug 21.1" = _t, #"Sep 21.1" = _t, #"Oct 21.1" = _t, #"Nov 21.1" = _t, #"Dec 21.1" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Description", type text}, {"Cost Type", type text}, {"Jan 20", Int64.Type}, {"Feb 20", Int64.Type}, {"Mar 20", Int64.Type}, {"Apr 20", Int64.Type}, {"May 20", Int64.Type}, {"Jun 20", Int64.Type}, {"Jul 20", Int64.Type}, {"Aug 20", Int64.Type}, {"Sep 20", Int64.Type}, {"Oct 20", Int64.Type}, {"Nov 20", Int64.Type}, {"Dec 20", Int64.Type}, {"Jan 21", Int64.Type}, {"Feb 21", Int64.Type}, {"Mar 21", Int64.Type}, {"Apr 21", Int64.Type}, {"May 21", Int64.Type}, {"Jun 21", Int64.Type}, {"Jul 21", Int64.Type}, {"Aug 21", Int64.Type}, {"Sep 21", Int64.Type}, {"Oct 21", Int64.Type}, {"Nov 21", Int64.Type}, {"Dec 21", Int64.Type}, {"Jan 20.1", type date}, {"Feb 20.1", type date}, {"Mar 20.1", type date}, {"Apr 20.1", type date}, {"May 20.1", type date}, {"Jun 20.1", type date}, {"Jul 20.1", type date}, {"Aug 20.1", type date}, {"Sep 20.1", type date}, {"Oct 20.1", type date}, {"Nov 20.1", type date}, {"Dec 20.1", type date}, {"Jan 21.1", type date}, {"Feb 21.1", type date}, {"Mar 21.1", type date}, {"Apr 21.1", type date}, {"May 21.1", type date}, {"Jun 21.1", type date}, {"Jul 21.1", type date}, {"Aug 21.1", type date}, {"Sep 21.1", type date}, {"Oct 21.1", type date}, {"Nov 21.1", type date}, {"Dec 21.1", type date}}),
  

    colsToUnpivot_ = List.Select(List.Select(Table.ColumnNames(#"Changed Type"), each not Text.Contains(_, ".1") ), each not List.Contains({"Description", "Cost Type"}, _)),  
    #"Unpivoted Columns" = Table.Unpivot(#"Changed Type", colsToUnpivot_, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Date", each Expression.Evaluate("["&[Attribute]&".1]", [_=_]), type date),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Description", "Cost Type", "Attribute", "Value", "Date"})
in
    #"Removed Other Columns"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

@red_arrowhead 

Place the following M code in a blank query to see the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdBNDsIgEAXgu7BuIp0fwKV6A3emYWd3TVx6fXkN6ItpAhMmZD5mWJZwX59hCpe2Jbag5xYstRDnU1sSZUYinOg3qdMQrhBwq46TdsFYEBbkX7ihTvA8BPcuOAvKgv6Ex7ptr/cYBI8n1KfckcSIMWIHyD6Ljfo8fiMz4oz4AbKPgyYM7ZTRSWGkMNKTWj8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, Subtype = _t, Jan = _t, Feb = _t, Mar = _t, Jan.1 = _t, Feb.1 = _t, Mar.1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Subtype", type text}, {"Jan", Int64.Type}, {"Feb", Int64.Type}, {"Mar", Int64.Type}, {"Jan.1", type date}, {"Feb.1", type date}, {"Mar.1", type date}}),

    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Type", "Subtype", "Jan.1", "Feb.1", "Mar.1"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Date", each Expression.Evaluate("["&[Attribute]&".1]", [_=_])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Jan.1", "Feb.1", "Mar.1"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Type", "Subtype", "Value", "Date", "Attribute"})
in
    #"Reordered Columns"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[red_arrowhead]

Thanks a lot for the help. This is working to chnage to the type I wanted. But I am unable to get the steps applied as I want to do the same with the actual data I have. I am very new to m code so wanted to know how can I understand the steps to be followed to achieve this result. 

It would be helpful. Thanks.

[CNENFRNL]

@red_arrowhead , @AlB 's solution is concise and advanced; in particular, the use of Expression.Evaluate() is beyond my comprehension in spite that I went throught quite a few articles on this advanced function. Here's an alternative,

    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Date", each Record.FieldOrDefault(_, [Attribute]&".1"))

In addition, I came up with a more laborious solution as follows,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc8xCsMwDAXQu3gOJJYl2x3b3qBbCd6aLdCx16+/UvOhZLAQSA99r2t4bK8whWt/svSSLr1o7iXOcZZFIiZs02jbNOwNGz4zdMmt0gqt/Ns7NgQnYc3cGm2iTbTPbd/fnxEbBzNkLs4zuZLrCffkOmQ5fl3IjdxOuIfHYUWEelyv5JX817b2BQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, Subtype = _t, Jan = _t, Feb = _t, Mar = _t, Jan.1 = _t, Feb.1 = _t, Mar.1 = _t]),
    #"All Columns" = Table.ToColumns(Source),
    #"Type Subtype" = List.Transform(List.Range(#"All Columns",0,2), each _ & _),
    #"Content Columns" = List.Transform(List.Zip(List.Split(List.Range(#"All Columns", 2), 3)), List.Combine),
    #"New Table" = Table.FromColumns(#"Type Subtype" & #"Content Columns", List.Range(Table.ColumnNames(Source),0,5)),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"New Table", {"Type", "Subtype"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

 not that elegant but easier to understand.

[AlB]

@CNENFRNL 

Good alternative. I tend to forget about Record.Field( ), although it's actually (simpler therefore) better.

Happy to elaborate on Expression.Evaluate( ). Given the kind of advanced stuff you regularly deploy in your answers, I'm sure you won't have any problem understanding it.

Your "more laborious" approach is interesting but it doesn't actually yield the expected result, or am I wrong?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

@red_arrowhead 

Can you share the actual data (or a fragment thereof that has the same structure)?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[red_arrowhead]

I tried to translate the m code o actual data I have but somewhere i am running into errors. Which steps to be actually followed to solve this. I am not able to put the chunk of actual data here as it is unable to post. But the actual columns  and one row of data are as below 

Description CostType Jan 20 Feb 20 Mar 20 Apr 20 May 20 Jun 20 Jul 20 Aug 20 Sep 20 Oct 20 Nov 20 Dec 20 Jan 21 Feb 21 Mar 21 Apr 21 May 21 Jun 21 Jul 21 Aug 21 Sep 21 Oct 21 Nov 21 Dec 21 Jan 20.1 Feb 20.1 Mar 20.1 Apr 20.1 May 20.1 Jun 20.1 Jul 20.1 Aug 20.1 Sep 20.1 Oct 20.1 Nov 20.1 Dec 20.1 Jan 21.1 Feb 21.1 Mar 21.1 Apr 21.1 May 21.1 Jun 21.1 Jul 21.1 Aug 21.1 Sep 21.1 Oct 21.1 Nov 21.1 Dec 21.1
A Red 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 100.00 1/29/2020 2/29/2020 3/29/2020 4/29/2020 5/29/2020 6/29/2020 7/29/2020 8/29/2020 9/29/2020 10/29/2020 11/29/2020 12/29/2020 1/29/2021 2/28/2021 3/29/2021 4/29/2021 5/29/2021 6/29/2021 7/29/2021 8/29/2021 9/29/2021 10/29/2021 11/29/2021 12/29/2021

I hope it is undertsandable. Please let me know. Thanks

[AlB]

@red_arrowhead 

Can you share the table above in an excel file? I can't get the columns organized correctly from what you posted above.

You have to share the URL to the file hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required).

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

Hi @red_arrowhead 

Can you please share the tables above, especially the first one, in text-tabular format instead of screencap so that the contents can be copied?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[red_arrowhead]

I am getting some problem while posting the table here. So, inserting the values as text. Hope it helps. 

Type Subtype Jan Feb Mar Jan Feb Mar
Red A 20 39 46 1/1/2021 2/1/2021 3/1/2021
Red B 21 35 23 1/4/2021 2/2/2021 3/2/2021
Red C 22 45 55 1/5/2021 2/3/2021 3/3/2021
Yellow A 23 65 67 1/6/2021 2/4/2021 3/4/2021
Yellow B 24 67 76 1/7/2021 2/5/2021 3/5/2021
Yellow C 25 43 87 1/8/2021 2/8/2021 3/8/2021

This should be the input. 

The output I want after power query is 

Type Subtype Value Date Attribute
Red A 20 1/1/2021 Jan
Red B 21 1/4/2021 Jan
Red C 22 1/5/2021 Jan
Yellow A 23 1/6/2021 Jan
Yellow B 24 1/7/2021 Jan
Yellow C 25 1/8/2021 Jan
Red A 39 2/1/2021 Feb
Red B 35 2/2/2021 Feb
Red C 45 2/3/2021 Feb
Yellow A 65 2/4/2021 Feb
Yellow B 67 2/5/2021 Feb
Yellow C 43 2/8/2021 Feb
Red A 46 3/1/2021 Mar
Red B 23 3/2/2021 Mar
Red C 55 3/3/2021 Mar
Yellow A 67 3/4/2021 Mar
Yellow B 76 3/5/2021 Mar
Yellow C 87 3/8/2021 Mar

Please have a look