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.
Hello All!
As most here I am hoping for some assistance. I to, am vary new to power Bi and its features. My goal is to group data in Power Query to look just like the below. I belive this is done through Grouping and also calculation? I pull the data in this format, when imported but just ends up like a plane table no filters or pivots ?
The goal is to segminet data accorss the Board to show just like the below.
-Grouped by Job# based on "Builder"
- Calculate "Number Of Jobs" by "Builder"
- Avg of "Days Since Sold"
- Avg of "Projected Sold to Close"
- Appear as the Below
I have 411 line that i would need to Transform in this way.
Job# | Builder | Days Since Sold | Projected Close Date |
00001-001 | Andy | Null | |
00001-010 | Andy | Null | |
00001-014 | Andy | 608 | 686 |
00001-020 | Andy | 741 | 810 |
00001-025 | Andy | 559 | 602 |
00001-032 | Andy | 699 | 714 |
Number of Jobs | 6 | Total Avg ? | Total Avg? |
00010-002 | Smith | 328 | 436 |
00010-025 | Smith | Null | Null |
00010-013 | Smith | 203 | 368 |
00010-019 | Smith | 307 | 481 |
00010-056 | Smith | 349 | 524 |
00010-033 | Smith | 236 | 448 |
00010-078 | Smith | 219 | 370 |
00010-024 | Smith | 207 | 372 |
Number Of jobs | 8 | Total Avg? | Total Avg? |
Thanks agian !!
NewStep=Table.Combine(Table.Group(PreviousStepName,"Builder",{"n",each _&#table(Table.ColumnNames(_),{{"Number Of Jobs",Table.RowCount(_),List.Average([Days Since Sold]),List.Average([Projected Close Date])}})})[n])
Oh, I get it, you are looking for the exact view in Power Query, then I don't think it is possible. Why because every aggregation that you do will create a column or transform the current one. So you can't create exact view that you are expecting.
Ok thankyou.
So then, would thier be away to achieve somthing similar? Lets say, in a drill down fashion?
Maybe drill down from a job# ? or would that end up creating a rediculous amount of colmns?
Hi Adrian,
You can use an option called "Group by", which will have advanced options to calculate as per your requirement.
You can add multiple groupings along with multiple calculations as mentioned below.
Also, It appears any grouping creates a new column rather than displaying in same columns
Thanks for your help!
Could you tell me what i would use to identify the Builder Column?
I Think i have that piece down however, when i attempt, the "Builder Colmn aways comes back with Error. I am usewr its not being identified as a value but then not sure how to identify value with words.
Could you be able to give me some sample data. I will look into it by checking the data. (You can remove or change the data if there is any confidential information)
Thankyou soo much for taking the time to look at this
Absolutley,
Sample Data below:
https://mega.nz/file/7UxxHbAK#15pI4F-LrbsUO1xWq5pfvPZTjBDB4gZz7j0tSVgqomk
Apologies the Number 6, and 8 where supposed to line up with the "Builder" Column
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.