Forum Discussion
Group data in power query
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 !!
9 Replies
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?
Apologies the Number 6, and 8 where supposed to line up with the "Builder" Column
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.
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)
Also, It appears any grouping creates a new column rather than displaying in same columns
Thanks for your help!
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])
