Unpivot more than one group of columns

[joel-abad]

Hi Everyone!

I have an Excel file like this one imported to Power BI:

And I want to unpivot the "Evidenced" and "Compliance" Columns, but keeping only one "Attribute" (Organization) column with 2 different "Values" (Evidenced and Compliance). It should look like this one:

I tried different things, but I am not being able to find a solution to this. Thank you everyone for the help!

[dufoq3 · Accepted solution]

Hi @joel-abad, for future requests provide data in usable format not as a screenshot! (if you don't know how - read note below my post)

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLLRyFCUpMz8jKTE3NgAq5lmSmpecmpKSABfUc4iSKeX6IQXJJYVIKhClkmVidayQhmbGRqMU4rQZpwWYshR7TVxkR4lhSbnfNzC3IyE/NK0NQhxEG2mqBYCPF1QFF+cmpxMW29a0qMdzFiEhcb0w58PJD9ZjSwHznIsbNBNpujJi98VqMHL3pQEwoClDSAFgAW+AMA4kIq5q5YAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, #"Safety Related" = _t, #"Security Related" = _t, #"RAM Related" = _t, #"Req_Object Type" = _t, #"Derived requirement" = _t, #"Org1 Evidenced" = _t, #"Org2 Evidenced" = _t, #"Org3 Evidenced" = _t, #"Overall Evidenced" = _t, #"Org1 Compliance" = _t, #"Org2 Compliance" = _t, #"Org3 Compliance" = _t, #"Overall Compliance" = _t]),
    UnpivotedOtherColumns = Table.UnpivotOtherColumns(Source, {"id", "Safety Related", "Security Related", "RAM Related", "Req_Object Type", "Derived requirement"}, "Attribute", "Value"),
    Ad_Organization = Table.AddColumn(UnpivotedOtherColumns, "Organization", each List.Accumulate({" Evidenced", " Compliance"}, [Attribute], (s,c)=> Text.BeforeDelimiter(s, c)), type text),
    Ad_Attribute2 = Table.AddColumn(Ad_Organization, "Attribute2", each Text.Trim(Text.AfterDelimiter([Attribute], [Organization])), type text),
    RemovedColumns = Table.RemoveColumns(Ad_Attribute2,{"Attribute"}),
    AddedSuffix = Table.TransformColumns(RemovedColumns, {{"Attribute2", each _ & " Status", type text}}),
    PivotedColumn = Table.Pivot(AddedSuffix, List.Distinct(AddedSuffix[Attribute2]), "Attribute2", "Value")
in
    PivotedColumn

[joel-abad]

Hi @dufoq3 OK! Sorry for that, I take note for the next time! Thank you very much for the solution, but I'd like to learn how to do it, like the steps to follow (if that's possible) instead copying and pasting a code. I'm not a programmer and I'm not very skilled in coding stuff. My apologies

[dufoq3]

Hi, each step (except Ad_Organization) was done via User Interface. If you want to also check List.Accumulate - Rick de Groot made great article here.

[joel-abad]

I got it!! Thank you very much 🙂

[dufoq3]

You're welcome 😉