Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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"
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"
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.
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"
Great - that's done the trick, and taught me quite a bit too!
Thanks for taking time to reply.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |