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 am trying to sum by Sale Item
example table below
Per ID Wk Sale Item
1 20 Bike
1 20 Car
2 20 Bike
3 21 Bike
3 22 Car
Just looking to get
Per ID Week Bike Car
1 20 1 1
2 20 1 0
3 21 1 0
3 22 0 1
Then Group by week
Wk Bike & Car Car Bike
20 1 0 1
21 0 0 1
22 0 1 0
I am interested in bringing back the zero's...Thanks
Solved! Go to Solution.
You'll need to test this as I threw in some quick logic to get your Bike and Car column
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtUvB0UdJRCs8GEsGJOakKniWpuUqxOtFKhkARIwMg4ZSZnYoq4pxYBBYwwlBiDBIxxBAxgmmKBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Added Custom" = Table.AddColumn(#"Promoted Headers", "Items", each 1),
#"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[#"Sale Item"]), "Sale Item", "Items", List.Count),
#"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Per ID"}),
#"Inserted Column" = Table.AddColumn(#"Removed Columns2", "Bike an Car", each List.Min({[Bike], [Car]}), type number),
#"Grouped Rows" = Table.Group(#"Inserted Column", {"Wk"}, {{"Car", each List.Min([Car]), type number}, {"Bike", each List.Min([Bike]), type number}, {"Bike And Car", each List.Max([Bike an Car]), type number}})
in
#"Grouped Rows"
This is close. Had it right up until you threw in the extra column at the end.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyABJOmdmpSrE6SCLOiUVgASMMJcYgEUMMESOYplgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Per ID" = _t, Wk = _t, #"Sale Item" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Per ID", Int64.Type}, {"Wk", Int64.Type}, {"Sale Item", type text}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[#"Sale Item"]), "Sale Item", "Per ID", List.Count)
in
#"Pivoted Column"
You'll need to test this as I threw in some quick logic to get your Bike and Car column
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtUvB0UdJRCs8GEsGJOakKniWpuUqxOtFKhkARIwMg4ZSZnYoq4pxYBBYwwlBiDBIxxBAxgmmKBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Added Custom" = Table.AddColumn(#"Promoted Headers", "Items", each 1),
#"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[#"Sale Item"]), "Sale Item", "Items", List.Count),
#"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Per ID"}),
#"Inserted Column" = Table.AddColumn(#"Removed Columns2", "Bike an Car", each List.Min({[Bike], [Car]}), type number),
#"Grouped Rows" = Table.Group(#"Inserted Column", {"Wk"}, {{"Car", each List.Min([Car]), type number}, {"Bike", each List.Min([Bike]), type number}, {"Bike And Car", each List.Max([Bike an Car]), type number}})
in
#"Grouped Rows"
Thanks to both of you replying! I will give it a spin today to see if I can get it working on my Data
Hi @HotChilli @Greg_Deckler ,
The logic is not working when I apply it to a large dataset, could you check the logic to see if there is a better way of doing it?
Thanks
Dubfelix
Can you post a larger sample dataset please? With some data that illustrates the issue.
Will do I think the issue is it is only counting 1 per week for all customers and maybe it should be a sum of the count?
Per ID Wk Sale Item
1 20 Bike
1 20 Car
2 20 Bike
3 21 Bike
3 22 Car
4 20 Bike
4 21 Bike
4 22 Car
4 22 Bike
5 20 Car
5 22 Bike
5 22 Car
6 20 Car
6 22 Bike
7 22 Car
7 21 Bike
8 21 Car
8 21 Bike
8 20 Car
8 20 Bike
8 22 Bike
PER_ID YEAR WEEK Number Count Per Week Count Once per Week TYPE
1 20 1 14 1 BIKE
1 20 1 3 1 CAR
1 19 2 2 1 CAR
1 19 4 5 1 BIKE
1 19 5 5 1 CAR
1 19 6 5 1 BIKE
1 19 7 5 1 CAR
1 19 8 5 1 BIKE
1 19 9 5 1 CAR
1 19 10 5 1 BIKE
1 19 11 5 1 CAR
1 19 12 5 1 BIKE
1 19 13 5 1 CAR
1 19 14 5 1 BIKE
1 19 15 5 1 CAR
1 19 16 5 1 BIKE
1 19 17 5 1 CAR
1 19 18 5 1 BIKE
1 19 19 5 1 CAR
2 20 1 5 1 CAR
2 20 1 5 1 BIKE
2 20 2 5 1 CAR
2 20 2 5 1 BIKE
2 20 4 5 1 CAR
2 20 4 5 1 BIKE
2 20 5 5 1 CAR
2 20 5 5 1 BIKE
2 20 6 5 1 CAR
2 20 6 5 1 BIKE
2 20 7 5 1 CAR
2 20 8 5 1 BIKE
2 20 9 5 1 CAR
2 20 10 5 1 BIKE
2 20 11 5 1 CAR
2 20 12 5 1 BIKE
2 20 13 5 1 CAR
2 20 14 5 1 BIKE
2 20 15 5 1 CAR
2 20 16 5 1 BIKE
2 20 17 5 1 CAR
2 20 18 5 1 BIKE
2 20 19 5 1 CAR
2 20 20 5 1 BIKE
3 20 1 4 1 CAR
3 20 1 4 1 BIKE
3 20 2 4 1 CAR
3 20 2 4 1 BIKE
3 20 3 4 1 CAR
3 20 3 4 1 BIKE
3 20 4 4 1 CAR
3 20 4 4 1 BIKE
3 20 5 4 1 CAR
3 20 5 4 1 BIKE
3 20 6 4 1 CAR
3 20 6 4 1 BIKE
3 20 7 4 1 CAR
3 20 7 4 1 BIKE
3 20 8 4 1 CAR
3 20 8 4 1 BIKE
3 20 9 4 1 CAR
3 20 9 4 1 BIKE
3 20 10 4 1 CAR
3 20 10 4 1 BIKE
3 20 11 4 1 CAR
3 20 11 4 1 BIKE
3 20 12 4 1 CAR
3 20 12 4 1 BIKE
3 20 13 4 1 CAR
3 20 13 4 1 BIKE
3 20 14 4 1 CAR
3 20 14 4 1 BIKE
3 20 15 4 1 CAR
3 20 15 4 1 BIKE
3 20 16 4 1 CAR
3 20 16 4 1 BIKE
3 20 17 4 1 CAR
3 20 17 4 1 BIKE
3 20 18 4 1 CAR
3 20 18 4 1 BIKE
3 20 19 4 1 CAR
3 20 19 4 1 BIKE
3 20 20 4 1 CAR
3 20 20 4 1 BIKE
4 20 1 5 1 CAR
4 20 1 5 1 BIKE
4 20 2 5 1 CAR
4 20 2 5 1 BIKE
4 20 4 5 1 CAR
4 20 4 5 1 BIKE
4 20 5 5 1 CAR
4 20 5 5 1 BIKE
4 20 6 5 1 CAR
4 20 6 5 1 BIKE
4 20 7 5 1 CAR
4 20 8 5 1 BIKE
4 20 9 5 1 CAR
4 20 10 5 1 BIKE
4 20 11 5 1 CAR
4 20 12 5 1 BIKE
4 20 13 5 1 CAR
4 20 14 5 1 BIKE
4 20 15 5 1 CAR
4 20 16 5 1 BIKE
4 20 17 5 1 CAR
4 20 18 5 1 BIKE
4 20 19 5 1 CAR
4 20 20 5 1 BIKE
PER_ID | YEAR | WEEK Number | Count Per Week | Count Once per Week | TYPE |
1 | 20 | 1 | 14 | 1 | BIKE |
1 | 20 | 1 | 3 | 1 | CAR |
1 | 19 | 2 | 2 | 1 | CAR |
1 | 19 | 4 | 5 | 1 | BIKE |
1 | 19 | 5 | 5 | 1 | CAR |
1 | 19 | 6 | 5 | 1 | BIKE |
1 | 19 | 7 | 5 | 1 | CAR |
1 | 19 | 8 | 5 | 1 | BIKE |
1 | 19 | 9 | 5 | 1 | CAR |
1 | 19 | 10 | 5 | 1 | BIKE |
1 | 19 | 11 | 5 | 1 | CAR |
1 | 19 | 12 | 5 | 1 | BIKE |
1 | 19 | 13 | 5 | 1 | CAR |
1 | 19 | 14 | 5 | 1 | BIKE |
1 | 19 | 15 | 5 | 1 | CAR |
1 | 19 | 16 | 5 | 1 | BIKE |
1 | 19 | 17 | 5 | 1 | CAR |
1 | 19 | 18 | 5 | 1 | BIKE |
1 | 19 | 19 | 5 | 1 | CAR |
2 | 20 | 1 | 5 | 1 | CAR |
2 | 20 | 1 | 5 | 1 | BIKE |
2 | 20 | 2 | 5 | 1 | CAR |
2 | 20 | 2 | 5 | 1 | BIKE |
2 | 20 | 4 | 5 | 1 | CAR |
2 | 20 | 4 | 5 | 1 | BIKE |
2 | 20 | 5 | 5 | 1 | CAR |
2 | 20 | 5 | 5 | 1 | BIKE |
2 | 20 | 6 | 5 | 1 | CAR |
2 | 20 | 6 | 5 | 1 | BIKE |
2 | 20 | 7 | 5 | 1 | CAR |
2 | 20 | 8 | 5 | 1 | BIKE |
2 | 20 | 9 | 5 | 1 | CAR |
2 | 20 | 10 | 5 | 1 | BIKE |
2 | 20 | 11 | 5 | 1 | CAR |
2 | 20 | 12 | 5 | 1 | BIKE |
2 | 20 | 13 | 5 | 1 | CAR |
2 | 20 | 14 | 5 | 1 | BIKE |
2 | 20 | 15 | 5 | 1 | CAR |
2 | 20 | 16 | 5 | 1 | BIKE |
2 | 20 | 17 | 5 | 1 | CAR |
2 | 20 | 18 | 5 | 1 | BIKE |
2 | 20 | 19 | 5 | 1 | CAR |
2 | 20 | 20 | 5 | 1 | BIKE |
3 | 20 | 1 | 4 | 1 | CAR |
3 | 20 | 1 | 4 | 1 | BIKE |
3 | 20 | 2 | 4 | 1 | CAR |
3 | 20 | 2 | 4 | 1 | BIKE |
3 | 20 | 3 | 4 | 1 | CAR |
3 | 20 | 3 | 4 | 1 | BIKE |
3 | 20 | 4 | 4 | 1 | CAR |
3 | 20 | 4 | 4 | 1 | BIKE |
3 | 20 | 5 | 4 | 1 | CAR |
3 | 20 | 5 | 4 | 1 | BIKE |
3 | 20 | 6 | 4 | 1 | CAR |
3 | 20 | 6 | 4 | 1 | BIKE |
3 | 20 | 7 | 4 | 1 | CAR |
3 | 20 | 7 | 4 | 1 | BIKE |
3 | 20 | 8 | 4 | 1 | CAR |
3 | 20 | 8 | 4 | 1 | BIKE |
3 | 20 | 9 | 4 | 1 | CAR |
3 | 20 | 9 | 4 | 1 | BIKE |
3 | 20 | 10 | 4 | 1 | CAR |
3 | 20 | 10 | 4 | 1 | BIKE |
3 | 20 | 11 | 4 | 1 | CAR |
3 | 20 | 11 | 4 | 1 | BIKE |
3 | 20 | 12 | 4 | 1 | CAR |
3 | 20 | 12 | 4 | 1 | BIKE |
3 | 20 | 13 | 4 | 1 | CAR |
3 | 20 | 13 | 4 | 1 | BIKE |
3 | 20 | 14 | 4 | 1 | CAR |
3 | 20 | 14 | 4 | 1 | BIKE |
3 | 20 | 15 | 4 | 1 | CAR |
3 | 20 | 15 | 4 | 1 | BIKE |
3 | 20 | 16 | 4 | 1 | CAR |
3 | 20 | 16 | 4 | 1 | BIKE |
3 | 20 | 17 | 4 | 1 | CAR |
3 | 20 | 17 | 4 | 1 | BIKE |
3 | 20 | 18 | 4 | 1 | CAR |
3 | 20 | 18 | 4 | 1 | BIKE |
3 | 20 | 19 | 4 | 1 | CAR |
3 | 20 | 19 | 4 | 1 | BIKE |
3 | 20 | 20 | 4 | 1 | CAR |
3 | 20 | 20 | 4 | 1 | BIKE |
4 | 20 | 1 | 5 | 1 | CAR |
4 | 20 | 1 | 5 | 1 | BIKE |
4 | 20 | 2 | 5 | 1 | CAR |
4 | 20 | 2 | 5 | 1 | BIKE |
4 | 20 | 4 | 5 | 1 | CAR |
4 | 20 | 4 | 5 | 1 | BIKE |
4 | 20 | 5 | 5 | 1 | CAR |
4 | 20 | 5 | 5 | 1 | BIKE |
4 | 20 | 6 | 5 | 1 | CAR |
4 | 20 | 6 | 5 | 1 | BIKE |
4 | 20 | 7 | 5 | 1 | CAR |
4 | 20 | 8 | 5 | 1 | BIKE |
4 | 20 | 9 | 5 | 1 | CAR |
4 | 20 | 10 | 5 | 1 | BIKE |
4 | 20 | 11 | 5 | 1 | CAR |
4 | 20 | 12 | 5 | 1 | BIKE |
4 | 20 | 13 | 5 | 1 | CAR |
4 | 20 | 14 | 5 | 1 | BIKE |
4 | 20 | 15 | 5 | 1 | CAR |
4 | 20 | 16 | 5 | 1 | BIKE |
4 | 20 | 17 | 5 | 1 | CAR |
4 | 20 | 18 | 5 | 1 | BIKE |
4 | 20 | 19 | 5 | 1 | CAR |
4 | 20 | 20 | 5 | 1 | BIKE |
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.