GROUP THE TABLE BY FIRST COLUMN

[SH-VE]

Hello , In my current report , I need to group my table by first column. For instance all the following columns of 0.018 X 47.375 should be together.

I am very thankful for any suggestion or help. Much Appreciated!

[Jimmy801]

Hello @SH-VE 

i can not follow what you exactly need. Is this only the visualisation you want to adapt? Because in the second screenshot all rows with the same item are grouped and without any data for the values.

For the visualisation you could add a index to your table, and insert it as rows like this

If you want to transform the data and group, you have to specify exactly what happens with every value when grouped (sum, first item, average etc). Here an example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIEYhOlWB0IzwiIjeE8Y7AIiJcEVWkO5iUDWaZwuRQgywwsD+KlAlkWQGypFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"Column2sum", each List.Sum([Column2]), type number}, {"Column3average", each List.Average([Column3]), type number}})
in
    #"Grouped Rows"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[SH-VE]

@Jimmy801 Sorry, I was unclear. What I need is the first visualization which you posted.

I created an index for column name  "gauge X width". But , I am not sure if I am using it correct.

Also, I am using a table format and not matrix . Is is possible to achieve the result in table?

basically, all the columns under same gauge X width should be grouped together and give totals for all the numeric columns at the end of the table . Thanks,

[Jimmy801]

Hello @SH-VE 

I'm not an expert in visualisations. You should post in the appropriate forum, not in power Query.

I think there is no possibilities in a table, because you are asking functionalities that are similar to a pivot. And pivot-table in Excel is Matrix in Power BI

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[lbendlin]

Use Table.ColumnNames() to find out the name of the first column. Then feed the first item in the resulting list  to your grouping call.

https://docs.microsoft.com/en-us/powerquery-m/table-columnnames