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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lucasconsult
Regular Visitor

Creating a new table from columns in an existing table resulting in one row and total amount

Exemple of what I have:

ColxxxxxColxxxxColxxxColxxxxxProdutoOrganizaçãoTotal amountColxxxx
xxxxxxxxxAX4xx
xxxxxxxxxBZ6xx
xxxxxxxxxCY5xx
xxxxxxxxxJZ7xx
xxxxxxxxxKW3xx


 


What I need as result -> 1 row for each column with total amount and column name inheritance (text).

ProdutoOrganizaçãoTotal amount
ProdutoOrganização25




Could you guys help me on this one?

 

 

Thanks!

1 ACCEPTED SOLUTION
v-yuta-msft
Community Support
Community Support

Hi lucasconsult,

 

You can use Unpivot Column, see M code below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqqioUNJRQiMcgTgCiE0gArE6ONQ5AXEUEJsRUOcMxJFAbEpAnRfUPHMC6ryBOByIjaHqYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Colxxxxx = _t, Colxxxx = _t, Colxxx = _t, Colxxxxx.1 = _t, Produto = _t, Organização = _t, #"Total amount" = _t, Colxxxx.1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Colxxxxx", type text}, {"Colxxxx", type text}, {"Colxxx", type text}, {"Colxxxxx.1", type text}, {"Produto", type text}, {"Organização", type text}, {"Total amount", Int64.Type}, {"Colxxxx.1", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Colxxxxx", "Colxxxx", "Colxxx", "Colxxxxx.1", "Organização", "Total amount", "Colxxxx.1"}, "Attribute", "Value"),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"Colxxxxx", "Colxxxx", "Colxxx", "Colxxxxx.1", "Total amount", "Colxxxx.1", "Attribute", "Value"}, "Attribute.1", "Value.1"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns1",{"Colxxxxx", "Colxxxx", "Colxxx", "Colxxxxx.1", "Colxxxx.1", "Value", "Value.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute.1", "Organização"}, {"Attribute", "Produto"}})
in
    #"Renamed Columns"

1.PNG 

After close&applied, create a measure using DAX:

Total amount_ = SUM(Table1[Total amount])

Then create a table chart which can meet your requirement as below:

2.PNG 

PBIX: https://www.dropbox.com/s/dj5pjcjljettr9l/Creating%20a%20new%20table%20from%20columns%20in%20an%20ex....

 

Regards,

Jimmy Tao

View solution in original post

1 REPLY 1
v-yuta-msft
Community Support
Community Support

Hi lucasconsult,

 

You can use Unpivot Column, see M code below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqqioUNJRQiMcgTgCiE0gArE6ONQ5AXEUEJsRUOcMxJFAbEpAnRfUPHMC6ryBOByIjaHqYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Colxxxxx = _t, Colxxxx = _t, Colxxx = _t, Colxxxxx.1 = _t, Produto = _t, Organização = _t, #"Total amount" = _t, Colxxxx.1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Colxxxxx", type text}, {"Colxxxx", type text}, {"Colxxx", type text}, {"Colxxxxx.1", type text}, {"Produto", type text}, {"Organização", type text}, {"Total amount", Int64.Type}, {"Colxxxx.1", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Colxxxxx", "Colxxxx", "Colxxx", "Colxxxxx.1", "Organização", "Total amount", "Colxxxx.1"}, "Attribute", "Value"),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"Colxxxxx", "Colxxxx", "Colxxx", "Colxxxxx.1", "Total amount", "Colxxxx.1", "Attribute", "Value"}, "Attribute.1", "Value.1"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns1",{"Colxxxxx", "Colxxxx", "Colxxx", "Colxxxxx.1", "Colxxxx.1", "Value", "Value.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute.1", "Organização"}, {"Attribute", "Produto"}})
in
    #"Renamed Columns"

1.PNG 

After close&applied, create a measure using DAX:

Total amount_ = SUM(Table1[Total amount])

Then create a table chart which can meet your requirement as below:

2.PNG 

PBIX: https://www.dropbox.com/s/dj5pjcjljettr9l/Creating%20a%20new%20table%20from%20columns%20in%20an%20ex....

 

Regards,

Jimmy Tao

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.