Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
anishkrissh
Frequent Visitor

Need to create a Two Column summarized table

I need to create a table of having all field name in the left column and it's summarized column in the right column. Is it possible?

1 ACCEPTED SOLUTION

Hi @anishkrissh ,

 

Choose all the columns in the table and unpivot it.

unp.PNG

 

After that, Group the table as the picture below.

group.PNG

 

For more details, please check the pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

6 REPLIES 6
v-frfei-msft
Community Support
Community Support

Hi @anishkrissh ,

 

summarizecolumns-function   should help you. BTW, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Order CountItem CountDiscountCouponsShipping AmountBlindDropShipChargeTaxAmount
51700.3610.9100
11001.7800
242602.1986.9400
11001.400

 

I want all of the column names as one column and all of it's summarized value in the next column.

Sorry can't share the data

Hi @anishkrissh ,

 

Does that meet your requirement?

 

Capture.PNG

 

M code for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlXSUTI0BxIGIKxnbAbiG+hZGsKElGJ1opVAPIQIkK1nboGiwMgEyDQyg4oZ6RlaAikLMz1LEwLmIMnHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Order Count" = _t, #"Item Count" = _t, Discount = _t, Coupons = _t, #"Shipping Amount" = _t, BlindDropShipCharge = _t, TaxAmount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Count", Int64.Type}, {"Item Count", Int64.Type}, {"Discount", Int64.Type}, {"Coupons", type number}, {"Shipping Amount", type number}, {"BlindDropShipCharge", Int64.Type}, {"TaxAmount", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each "a"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Custom", {"Custom"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Attribute"}, {{"value", each List.Sum([Value]), type number}})
in
    #"Grouped Rows"

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Can you show me how you did that?

Hi @anishkrissh ,

 

Choose all the columns in the table and unpivot it.

unp.PNG

 

After that, Group the table as the picture below.

group.PNG

 

For more details, please check the pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
DuarteRaminhosB
Resolver III
Resolver III

Hi @anishkrissh,

 

You can use summarizecolumns to do so.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.