Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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?
Solved! Go to Solution.
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
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
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
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.
@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.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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
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
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
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
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
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
33 | |
16 | |
12 | |
11 | |
9 |
User | Count |
---|---|
44 | |
24 | |
16 | |
14 | |
14 |