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

[lucasconsult]

Exemple of what I have:

Colxxxxx	Colxxxx	Colxxx	Colxxxxx	Produto	Organização	Total amount	Colxxxx
xxx	xx	xx	xx	A	X	4	xx
xxx	xx	xx	xx	B	Z	6	xx
xxx	xx	xx	xx	C	Y	5	xx
xxx	xx	xx	xx	J	Z	7	xx
xxx	xx	xx	xx	K	W	3	xx

 


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

Produto	Organização	Total amount
Produto	Organização	25

Could you guys help me on this one?

Thanks!

[v-yuta-msft · Accepted solution]

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"

 

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:

 

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

Regards,

Jimmy Tao