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,
I have a query with multiple AFE numbers. Within each afe number are a couple of major codes and minor codes. My end goal is to sum the costs associated with each AFE's major and minor cost so that I can have 1 row per AFE, major and minor code. Once the query is in this format, I will be able to create a one to many relationship with another query to transfer these values using the related function. I believe this has to be done in the "transform data" query editor otherwise I'll end up with a many to many relationship correct?
The first table is an example of what my data looks like. The second table is the end result I'm shooting for.
Thank you in advance!
AFE | Major | Minor | Cost |
10 | 1 | 300 | 10 |
10 | 1 | 300 | 20 |
10 | 1 | 400 | 10 |
10 | 1 | 400 | 5 |
20 | 1 | 300 | 20 |
20 | 1 | 300 | 10 |
20 | 1 | 400 | 20 |
20 | 2 | 400 | 10 |
20 | 2 | 400 | 20 |
AFE | Major | Minor | Cost |
10 | 1 | 300 | 30 |
10 | 1 | 400 | 15 |
20 | 1 | 300 | 30 |
20 | 1 | 400 | 20 |
20 | 2 | 400 | 30 |
Solved! Go to Solution.
Just use the Group By feature of the Query Editor.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lEyBGJjAzDLQClWB0PUCE3UBKtaiKgpWNAIqwFGWC0zQjUAWa0RumVooiC1sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [AFE = _t, Major = _t, Minor = _t, Cost = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"AFE", Int64.Type}, {"Major", Int64.Type}, {"Minor", Int64.Type}, {"Cost", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"AFE", "Major", "Minor"}, {{"Cost", each List.Sum([Cost]), type number}})
in
#"Grouped Rows"
Just use the Group By feature of the Query Editor.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lEyBGJjAzDLQClWB0PUCE3UBKtaiKgpWNAIqwFGWC0zQjUAWa0RumVooiC1sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [AFE = _t, Major = _t, Minor = _t, Cost = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"AFE", Int64.Type}, {"Major", Int64.Type}, {"Minor", Int64.Type}, {"Cost", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"AFE", "Major", "Minor"}, {{"Cost", each List.Sum([Cost]), type number}})
in
#"Grouped Rows"
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 |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |