Pivot column with duplicate attribute

[YasserEE]

Hi there Power BI Experts,

I am running into an issue where I have duplicate "attribute" values where I cannot convert them into columns.

I'm sharing just an example of this, in reality, I have over 30 other Attributes with the same issue.

Current format

companyid	value	attribute
5608	101	CompLegalCountry
5608	101	CompLocationCountry
5608	13001	CompIndustry
5608	22	CompStockId
5608	23	CompStockId

Wanted Format

companyid	CompLegalCountry	CompLocationCountry	CompIndustry	CompStockId	CompStockId.1
5608	101	101	13001	22	23

Any help is much appreciated:)

[BA_Pete · Accepted solution]

Hi @YasserEE ,

It's a bit fiddly, but here's how I approached it:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjUzsFDSUTI0MASSzvm5BT6p6Yk5zvmleSVFlUqxOthU5CcnlmTm52FTZGxgYKgAVeeZl1JajKbAyAgqGVySn5ztmYIiZ4wuFwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"companyid " = _t, value = _t, attribute = _t]),
    groupCompAndAttrib = Table.Group(Source, {"companyid ", "attribute"}, {{"data", each _, type table [#"companyid "=nullable text, #" value"=nullable text, #" attribute"=nullable text]}}),
    addNestedIndex = Table.TransformColumns(groupCompAndAttrib, {"data", each Table.AddIndexColumn(_, "index", 0, 1)}),
    expandNestedData = Table.ExpandTableColumn(addNestedIndex, "data", {"value", "index"}, {"value", "index"}),
    mergeAttribAndIndex = Table.CombineColumns(Table.TransformColumnTypes(expandNestedData, {{"index", type text}}, "en-GB"),{"attribute", "index"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"attribute"),
    pivotAttribute = Table.Pivot(mergeAttribAndIndex, List.Distinct(mergeAttribAndIndex[attribute]), "attribute", "value")
in
    pivotAttribute

Summary:

1) Group table on [companyid] and [attribute] using 'All Rows' aggregator to create nested tables.

2) Add index column to nested tables to identify different instances of the same [attribute].

3) Expand nested tables back out and merge [attribute] with [index] with a '.' delimiter

4) Pivot [attribute] using [value] as value and set as 'Do not aggregate'.

This gives me the following output:

Pete

[BA_Pete]

If you want the attribute headers to be exactly how you described then it's a bit more fiddly again:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjUzsFDSUTI0MASSzvm5BT6p6Yk5zvmleSVFlUqxOthU5CcnlmTm52FTZGxgYKgAVeeZl1JajKbAyAgqGVySn5ztmYIiZ4wuFwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"companyid " = _t, value = _t, attribute = _t]),
    groupCompAndAttrib = Table.Group(Source, {"companyid ", "attribute"}, {{"data", each _, type table [#"companyid "=nullable text, #" value"=nullable text, #" attribute"=nullable text]}}),
    addNestedIndex = Table.TransformColumns(groupCompAndAttrib, {"data", each Table.AddIndexColumn(_, "index", 0, 1)}),
    expandNestedData = Table.ExpandTableColumn(addNestedIndex, "data", {"value", "index"}, {"value", "index"}),
    replaceIfDuplicated = Table.ReplaceValue(expandNestedData, each [attribute], each if [index] > 0 then Text.Combine({[attribute], ".", Text.From([index])}) else [attribute], Replacer.ReplaceText,{"attribute"}),
    removeIndex = Table.RemoveColumns(replaceIfDuplicated,{"index"}),
    pivotAttribute = Table.Pivot(removeIndex, List.Distinct(removeIndex[attribute]), "attribute", "value")
in
    pivotAttribute

This uses a conditional replace instead of a column merge, but gives cleaner output:

Pete