Forum Discussion
Pivot within a pivot
Hi,
I have the following example dataset:
| Name | Task | Days | Day |
| 1 | A | 1 | 01/01/2020 |
| 1 | A | 1 | 02/01/2020 |
| 1 | B | 1 | 03/01/1900 |
| 1 | B | 1 | 04/01/2020 |
| 1 | B | 1 | 05/01/2020 |
| 2 | A | 1 | 01/01/2020 |
| 2 | A | 1 | 02/01/2020 |
| 2 | B | 1 | 03/01/1900 |
| 2 | B | 1 | 04/01/2020 |
| 2 | B | 1 | 05/01/2020 |
I am trying to achieve something similar to the below:
| Name | Task | Days | Week |
| 1 | A | 2 | 01/01/2020 |
| 1 | B | 3 | 01/01/2020 |
| 2 | A | 2 | 01/01/2020 |
| 2 | B | 3 | 01/01/2020 |
Hi Anonymous ,
I am not sure whether "03/01/1900" is mis-writing? If so, you also could refer to below M code for details.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYhBtYKgPREYGRgZKsTpoUkYYUk4wKWPcUia4pUxRpIxwO8MItzOMcDvDCLczjHA4IxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, #"Task " = _t, Days = _t, Day = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", Int64.Type}, {"Task ", type text}, {"Days", Int64.Type}, {"Day", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"minDay", each List.Min([Day]), type date}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Name"}, #"Grouped Rows", {"Name"}, "Grouped Rows", JoinKind.RightOuter), #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"minDay"}, {"minDay"}), #"Grouped Rows1" = Table.Group(#"Expanded Grouped Rows", {"Name", "Task "}, {{"Count", each Table.RowCount(_), type number}, {"min", each List.Min([minDay]), type date}}) in #"Grouped Rows1"Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi Anonymous
What is the logic for the last column?? Always 01/01/2020? If so:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYhBtYKgPREYGRgZKsTpoUkYYUk4wKWOQlKGlARYpE9y6TFGkjHA7wwi3M4xwO8MItzOMcDgjFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Task " = _t, Days = _t, Day = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", Int64.Type}, {"Task ", type text}, {"Days", Int64.Type}, {"Day", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Name", "Task "}, {{"Days", each Table.RowCount(_), type number}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Week", each #date(2020,1,1), type date) in #"Added Custom"Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Anonymousin case you are looking to pivot mulitple times, you may have a look at below blog
https://kohera.be/blog/power-bi/how-to-unpivot-twice/
Else, please provide the output that you are looking for clearly.
Hi Anonymous ,
I am not sure whether "03/01/1900" is mis-writing? If so, you also could refer to below M code for details.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYhBtYKgPREYGRgZKsTpoUkYYUk4wKWPcUia4pUxRpIxwO8MItzOMcDvDCLczjHA4IxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, #"Task " = _t, Days = _t, Day = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", Int64.Type}, {"Task ", type text}, {"Days", Int64.Type}, {"Day", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"minDay", each List.Min([Day]), type date}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Name"}, #"Grouped Rows", {"Name"}, "Grouped Rows", JoinKind.RightOuter), #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"minDay"}, {"minDay"}), #"Grouped Rows1" = Table.Group(#"Expanded Grouped Rows", {"Name", "Task "}, {{"Count", each Table.RowCount(_), type number}, {"min", each List.Min([minDay]), type date}}) in #"Grouped Rows1"Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
