Forum Discussion
pivot, unpivot or transpose - or something else?
I have looked through that many tutorials, it feels as if I am "unlearning" Power BI!
I have some data which looks like this:
and I want it to look like this:
How do I do it?
Okay, so I've done this by manipulating nested tables and left each step distinct so it's easier to follow/reproduce, but it could quite feasibly be condensed into fewer steps.
It's not pretty either way but, here it is:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZJBb8IwDIX/StQTkxASLQw4dpMGHNiFIQ6Ig2ndNVIbS64R279fUqBwgLTSTrWj7z07r9ntggORVIOEyqAfvLnaft/BQAq2GAb7/mNkjlyC+fUyK/zRCXmRT5QcuQCTVqpnyxcv/cFgErRFGD1lNkYLpmotIFgpylRcIusEGvtJLc2hyIjTq3pxad0Qbdw+zSLPySVjR/IurWkL+iiRsEXTeufX2uBEVJyIJb9abJuDLibReXUB/ka5OHzVTRf12COOj5UwFNo9uZGHa95l5IFuWftG3v5d6Ju4pqPkKs7cVWw7+08CLr/9Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Domain = _t, name = _t, country = _t, count = _t]), chgTypes = Table.TransformColumnTypes(Source,{{"Domain", type text}, {"name", type text}, {"country", type text}, {"count", Int64.Type}}), sortDomainCountry = Table.Sort(chgTypes,{{"Domain", Order.Ascending}, {"country", Order.Ascending}}), // Most relevant steps from here --------> groupDomainName = Table.Group(sortDomainCountry, {"Domain", "name"}, {{"data", each _, type table [Domain=nullable text, name=nullable text, country=nullable text, count=nullable number]}}), addNestedIndex = Table.TransformColumns(groupDomainName, {"data", each Table.AddIndexColumn(_, "Index", 1, 1)}), addNestedCountNo = Table.TransformColumns(addNestedIndex, {"data", each Table.AddColumn(_, "countNumber", each Text.Combine({"Count", Text.From([Index])}))}), addNestedCountryNo = Table.TransformColumns(addNestedCountNo, {"data", each Table.AddColumn(_, "countryNumber", each Text.Combine({"Country", Text.From([Index])}))}), pivotNestedCounts = Table.TransformColumns(addNestedCountryNo, {"data", each Table.Pivot(_, [countNumber], "countNumber", "count")}), pivotNestedCountries = Table.TransformColumns(pivotNestedCounts, {"data", each Table.Pivot(_, [countryNumber], "countryNumber", "country")}), fillUpNestedCols = Table.TransformColumns(pivotNestedCountries, {"data", each Table.FillUp(_, List.Select(Table.ColumnNames(_), each Text.StartsWith(_, "Count")))}), expandDataCol = Table.ExpandTableColumn(fillUpNestedCols, "data", {"Country1", "Count1", "Country2", "Count2", "Country3", "Count3", "Country4", "Count4", "Country5", "Count5", "Country6", "Count6", "Country7", "Count7"}, {"Country1", "Count1", "Country2", "Count2", "Country3", "Count3", "Country4", "Count4", "Country5", "Count5", "Country6", "Count6", "Country7", "Count7"}), filterRedundantRows = Table.SelectRows(expandDataCol, each ([Count1] <> null)) in filterRedundantRowsTo get this output:
The initial 'sortRows' step is optional and just depends if there's a specific order you want your column values to come out in.
Pete
6 Replies
Hi AndrewPF ,
This looks like an XY Problem to me.
What is it you're actually trying to achieve overall by getting the data into this shape?
You're moving the structure away from the optimal setup, so I'm presuming you're trying to achieve some other goal with this?
Pete
The source is a JSON file. Most of the data is Variables or Records, which split out horizontally. The screen shots are the only Lists in the data - these split out vertically, artificially duplicating the number of records if they are all done together. What I normally do is separate the Lists, transpose / pivot / group, then merge back into the main data.
Okey dokey, gotcha.
Any chance you could send your first screenshot as copyable data please?
Either copy from Excel and paste directly into a post here, or paste into Enter Data in Power Query then copy/paste the M code from that here.
Pete
Here it is:
Domain name country count boots.com Boots Canada 1 boots.com Boots Germany 1 boots.com Boots Mexico 1 boots.com Boots Netherlands (the) 1 boots.com Boots France 23 boots.com Boots United States of America (the) 7 halfords.com Halfords Finland 1 halfords.com Halfords Ireland 1 halfords.com Halfords Germany 18 halfords.com Halfords Netherlands (the) 2 halfords.com Halfords United States of America (the) 6 woolworths.com Woolworths United States of America (the) 38 target.com Target United States of America (the) 5 target.com Target Australia 4 target.com Target Canada 3 target.com Target Germany 5 target.com Target Ireland 24 target.com Target South Africa 9 target.com Target United States of America (the) 38 Okay, so I've done this by manipulating nested tables and left each step distinct so it's easier to follow/reproduce, but it could quite feasibly be condensed into fewer steps.
It's not pretty either way but, here it is:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZJBb8IwDIX/StQTkxASLQw4dpMGHNiFIQ6Ig2ndNVIbS64R279fUqBwgLTSTrWj7z07r9ntggORVIOEyqAfvLnaft/BQAq2GAb7/mNkjlyC+fUyK/zRCXmRT5QcuQCTVqpnyxcv/cFgErRFGD1lNkYLpmotIFgpylRcIusEGvtJLc2hyIjTq3pxad0Qbdw+zSLPySVjR/IurWkL+iiRsEXTeufX2uBEVJyIJb9abJuDLibReXUB/ka5OHzVTRf12COOj5UwFNo9uZGHa95l5IFuWftG3v5d6Ju4pqPkKs7cVWw7+08CLr/9Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Domain = _t, name = _t, country = _t, count = _t]), chgTypes = Table.TransformColumnTypes(Source,{{"Domain", type text}, {"name", type text}, {"country", type text}, {"count", Int64.Type}}), sortDomainCountry = Table.Sort(chgTypes,{{"Domain", Order.Ascending}, {"country", Order.Ascending}}), // Most relevant steps from here --------> groupDomainName = Table.Group(sortDomainCountry, {"Domain", "name"}, {{"data", each _, type table [Domain=nullable text, name=nullable text, country=nullable text, count=nullable number]}}), addNestedIndex = Table.TransformColumns(groupDomainName, {"data", each Table.AddIndexColumn(_, "Index", 1, 1)}), addNestedCountNo = Table.TransformColumns(addNestedIndex, {"data", each Table.AddColumn(_, "countNumber", each Text.Combine({"Count", Text.From([Index])}))}), addNestedCountryNo = Table.TransformColumns(addNestedCountNo, {"data", each Table.AddColumn(_, "countryNumber", each Text.Combine({"Country", Text.From([Index])}))}), pivotNestedCounts = Table.TransformColumns(addNestedCountryNo, {"data", each Table.Pivot(_, [countNumber], "countNumber", "count")}), pivotNestedCountries = Table.TransformColumns(pivotNestedCounts, {"data", each Table.Pivot(_, [countryNumber], "countryNumber", "country")}), fillUpNestedCols = Table.TransformColumns(pivotNestedCountries, {"data", each Table.FillUp(_, List.Select(Table.ColumnNames(_), each Text.StartsWith(_, "Count")))}), expandDataCol = Table.ExpandTableColumn(fillUpNestedCols, "data", {"Country1", "Count1", "Country2", "Count2", "Country3", "Count3", "Country4", "Count4", "Country5", "Count5", "Country6", "Count6", "Country7", "Count7"}, {"Country1", "Count1", "Country2", "Count2", "Country3", "Count3", "Country4", "Count4", "Country5", "Count5", "Country6", "Count6", "Country7", "Count7"}), filterRedundantRows = Table.SelectRows(expandDataCol, each ([Count1] <> null)) in filterRedundantRowsTo get this output:
The initial 'sortRows' step is optional and just depends if there's a specific order you want your column values to come out in.
Pete
Just what I needed, thanks.
