Group data in power query

[Adrian_A]

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 !!

[wdx223_Daniel]

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])

[Sri_phani]

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. 

[Adrian_A]

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?

[Sri_phani]

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. 

[Adrian_A]

Also, It appears any grouping creates a new column rather than displaying in same columns

Thanks for your help!

[Adrian_A]

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.

[Sri_phani]

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)

[Adrian_A]

Thankyou soo much for taking the time to look at this

Absolutley,

Sample Data below:

https://mega.nz/file/7UxxHbAK#15pI4F-LrbsUO1xWq5pfvPZTjBDB4gZz7j0tSVgqomk

[Adrian_A]

Apologies the Number 6, and 8 where supposed to line up with the "Builder" Column