Pivot within a pivot

[rossmccoriston]

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

[dax · Accepted solution]

Hi @rossmccoriston , 

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 Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[negi007]

@rossmccoristonin 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. 

[AlB]

Hi @rossmccoriston 

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