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

Question

Exemple of what I have:ColxxxxxColxxxxColxxxColxxxxxProdutoOrganizaçãoTotal amountColxxxxxxxxxxxxxAX4xxxxxxxxxxxBZ6xxxxxxxxxxxCY5xxxxxxxxxxxJZ7xxxxxxxxxxxKW3xx&nbsp;What I need as result -&gt; 1 row for each column with total amount and column name inheritance (text).ProdutoOrganizaçãoTotal amountProdutoOrganização25Could you guys help me on this one?&nbsp;&nbsp;Thanks!

v-yuta-msft · Accepted Answer

Hi&nbsp;lucasconsult,
&nbsp;
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"
&nbsp;
After close&amp;applied, create a measure using DAX:
Total amount_ = SUM(Table1[Total amount])
Then create a table chart which can meet your requirement as below:
&nbsp;
PBIX: https://www.dropbox.com/s/dj5pjcjljettr9l/Creating%20a%20new%20table%20from%20columns%20in%20an%20existing%20table%20resulting%20in%20one%20row%20and%20total%20amount.pbix?dl=0.
&nbsp;
Regards,
Jimmy Tao

Forum Discussion

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

1 Reply

Related content

Cannot create liquid clustering on existing unpartitioned table

Create title from a result out of existing Measure!

Create Data Pipeline with existing definition using Terraform

Download cypher results

Grouping returns "Other" results despite those values being in existing groups

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