Forum Discussion
Change rows to columns
Anonymous - Take a look at my file link again, second tab. I created 1 measure that is a simple SUM forumula. I then put it in a Matrix, and put the line number in the columns, everything else in rows, and got rid of the subtotals, except for the Transction line, which I thought might be useful, but you can get rid of that too. Then expanded all row descriptions and removed the stepped layout default.
That will automatically expand for every line item, so if a transaction comes in with 6 line items, you'll have 6 columns of data.
You could put the product names in the columns, but you have 13 unique products, so that becomes a very wide table to show all 13, and I suspect your real data has even more actual products.
You could combine this process with the measure MFelix provided if you don't want actual quantities but the concatenation of the product name and total in the Values part. I did a rough version of that on tab 3.
You can pre-expand the entire matrix so your users don't have to, and remove the +/- expansion buttons if desired, also shown on tab 3
Hi Anonymous
Go to powerquery and create blank query and paste below in the advance editor and see the steps, this will provide you exactly what you want, let me know if not clear.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeNEIDZVitWBiBgBcRJcxAhDjRFWNcZAnIwiYgLEKXARY6g5qSgiIHVpKCIgnI4iAjInAyISCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Transaction = _t, Seuquence = _t, Product = _t, Qty = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Transaction", Int64.Type}, {"Seuquence", Int64.Type}, {"Product", type text}, {"Qty", Int64.Type}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Qty", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Products", each [Product]&[Qty]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Product", "Qty"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Seuquence", type text}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type2", "prod", each "Poduct" & " "&[Seuquence]),
#"Grouped Rows" = Table.Group(#"Added Custom1", {"Transaction"}, {{"combine", each Text.Combine([Products],"; "), type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "combine", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"combine.1", "combine.2", "combine.3", "combine.4"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"combine.1", type text}, {"combine.2", type text}, {"combine.3", type text}, {"combine.4", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"combine.1", "Product1"}, {"combine.2", "Product2"}, {"combine.3", "Product3"}, {"combine.4", "Product4"}})
in
#"Renamed Columns"