Power Query Unpivot Column

[coding7]

Hi, 

I have this sample table, and I would like to apply an unpivot on the Matched Information Types colmns

Repository	File Name	Status	Comment	Current Label	Current Label ID	Applied Label	Applied Label ID	Condition Name	Matched String	Matched Information Types	Last Modified
Repository 1	FileName 1	Success	No label or action to apply	Not set						"[ID Card Number: 342141234,131343124,13511341324,14432561414,134123312], [Drivers License Number: 32424112], [National ID: 2412312312]"	2024-02-23 14:18:15Z
Repository 3	FileName 3	Success	No label or action to apply	Not set						[Social Welfare Number: 2312321], [Tax Identification Number: 23123]	2019-08-09 08:49:58Z

The final output would be:

Repository

File Name

Status

Comment

Current Label

Current Label ID

Applied Label

Applied Label ID

Condition Name

Matched String

Attribute

value

Matched Information Types

Last Modified

Last Modified By

Repository 1

FileName 1

Success

No label or action to apply

Not set

ID Card Number

342141234,131343124,13511341324,14432561414,134123312

"[ID Card Number: 342141234,131343124,13511341324,14432561414,134123312], [Drivers License Number: 32424112], [National ID: 2412312312]"

2024-02-23 14:18:15Z

Repository 1

FileName 1

Success

No label or action to apply

Not set

Drivers License Number

32424112

"[ID Card Number: 342141234,131343124,13511341324,14432561414,134123312], [Drivers License Number: 32424112], [National ID: 2412312312]"

2024-02-23 14:18:15Z

Repository 1

FileName 1

Success

No label or action to apply

Not set

National ID

2412312312

"[ID Card Number: 342141234,131343124,13511341324,14432561414,134123312], [Drivers License Number: 32424112], [National ID: 2412312312]"

2024-02-23 14:18:15Z

Repository 3

FileName 3

Success

No label or action to apply

Not set

Social Welfare Number

2312321

[Social Welfare Number: 2312321], [Tax Identification Number: 23123]

2019-08-09 08:49:58Z

Repository 3

FileName 3

Success

No label or action to apply

Not set

Tax Identification Number

23123

[Social Welfare Number: 2312321], [Tax Identification Number: 23123]

2019-08-09 08:49:58Z

The Matched Information Types column can have more values and columns, it can be arbitrary

Is it possibe to achieve this in power query ?

Thanks a lot !

[parry2k]

@coding7 Awsome - I was about to share a video from my YT channel which talks about Pivoting. Anyhow, glad to hear you have a solution. Cheers!

[parry2k · Accepted solution]

@coding7 here is one way to do this, start a blank query, click advanced editor and paste the M code below. You can follow the same steps on the real data.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZBBa8MwDIX/ivA5hUp2xsh1YRAYObSDwUIOrquCwY2D7Zb138/2Dl1hx4EFz/KT+fSmSex49dEmH26AohGv1vGoz1wv+4sxHGNWowenD+zAB9AmWb9A8qDX1d3qa4LIKSv4o6ahhxcdjjBezgcOHUhFqJCkalCiVBKpqBazRlm0UpLap+wp/eLMlrmBqQ/2yiHCmzW8RL5/SIoU/nhGXei0g6HvgMpwPXMGoS2pzZY2JAFVh88dtp9ibh4ykL8zkP+Xwd4bm6E+2J10uJNXOsIC/q6/YDjykuzJmrrEo6tsIOb5Gw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Repository = _t, #"File Name" = _t, Status = _t, Comment = _t, #"Current Label" = _t, #"Current Label ID" = _t, #"Applied Label" = _t, #"Applied Label ID" = _t, #"Condition Name" = _t, #"Matched String" = _t, #"Matched Information Types" = _t, #"Last Modified" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Repository", type text}, {"File Name", type text}, {"Status", type text}, {"Comment", type text}, {"Current Label", type text}, {"Current Label ID", type text}, {"Applied Label", type text}, {"Applied Label ID", type text}, {"Condition Name", type text}, {"Matched String", type text}, {"Matched Information Types", type text}, {"Last Modified", type datetime}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Matched Information Types", Splitter.SplitTextByDelimiter("],", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Matched Information Types"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Matched Information Types", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "New Types", each Text.AfterDelimiter([Matched Information Types], "[")),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Added Custom", "New Types", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Attribute", "Values"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Attribute", type text}, {"Values", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Matched Information Types"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns","]","",Replacer.ReplaceText,{"Values"})
in
    #"Replaced Value"

[coding7]

Sorry @parry2k , I already got it, I just followed this video Pivot Two Columns to Multiple Columns with Power Query (youtube.com) 

[coding7]

Thanks a lot @parry2k is working perfectly ✌️

Just a small question, if I want to later pivot the columns 'Attribute' and 'Values' with the rest, do you know how to apply it?

For example:

ID Card Number Drivers License Number National ID Social Welfare Number Tax Identification Number

342141234,131343124,13511341324,14432561414,134123312

32424112

2412312312

2312321

23123

Once again, thank you for everything