pivot, unpivot or transpose - or something else?

[AndrewPF]

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? 

[AndrewPF]

Just what I needed, thanks. 

[AndrewPF]

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

[BA_Pete · Accepted solution]

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
    filterRedundantRows

To 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

[AndrewPF]

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. 

[BA_Pete]

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

[BA_Pete]

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