Pivoting a table with a nasty twist

[Anonymous]

Hi all. I'm struggling with the table below, and also new to PowerBI. I've already tried using DAX, matrix reports, and so on but have got stuck on the badly placed date column. Any help would be much appreciated.

My initial table looks like this:

Task	Type	Value	Date
1	A	24
1	B	25
1	C	26
1	Delivery		25/04/2021
2	A	31
2	B	32
2	C	33
2	Delivery		21/03/2021

I want to get the table into the following form so I can then use it for reports:

Delivery Date	Task	A	B	C
24/05/2021	1	24	25	26
21/03/2021	2	31	32	33

The final objective is to summarize by year:

Year	SumA	SumB	SumC
2021	55	57	59

As I said, any help would be very much appreciated!

[AlexisOlson]

Use Fill Up on the Date column, remove the Delivery rows, and then pivot Value.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYiMTIKEUqwMRcQKJmCKLOINEzJBFXFJzMstSiyqBTAWwcn0DE30jAyNDsAojqLnGhjA9RlBzjY2QRUDmGhsji6Cba6hvYAw1NxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Task = _t, Type = _t, Value = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Task", Int64.Type}, {"Type", type text}, {"Value", Int64.Type}, {"Date", type date}}),
    #"Filled Up" = Table.FillUp(#"Changed Type",{"Date"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Type] <> "Delivery")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Type]), "Type", "Value")
in
    #"Pivoted Column"

[Anonymous]

Thanks for this. Trying to apply your solution on my full data, I've run into a problem, however. There isn't always a delivery date, so a simple fillup results in some tasks getting an incorrect date. Is there some way to only fill in where the task number is the same?

Also, I'm using a direct link to an SQL database. Not sure if that could potentially be an issue.

[AlexisOlson · Accepted solution]

A slightly different approach would be to combine the last two columns as a new custom column and then pivot Type with that new column as the values.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYiMTIKGgFKsDEXICCZmiCDmDhMxQhFxSczLLUosqwYIgDfoGJvpGBkaGYBVGUKONDeGajKBGGxuhCIGMNjZGEUI32lDfwBhqdCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Task = _t, Type = _t, Value = _t, Date = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if [Type] = "Delivery" then [Date] else [Value]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value", "Date"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Type]), "Type", "Custom"),
    #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}, {"Delivery", type date}})
in
    #"Changed Type"

[Anonymous]

Great - that's done the trick, and taught me quite a bit too!

Thanks for taking time to reply.