Tag column values with the header

[Anonymous]

Hi!

I am trying to create a table out of a list of items which has individual header names and below each header there are 7 options. When bringing the data from excel into Power BI to make the values distinct I want to tag the Options with the header name so its distinct. Wondering if it can be done and how to achieve it.

Raw data:

Availibility

Option 1

Option 2

Option 3

Option 4

Option 5

Option 6

Option 7

Purchase

Option 1

Option 2

Option 3

Option 4

Option 5

Option 6

Option 7

Expected to see something like this :

Option 1 - Availibility

Option 2 - Availibility

Option 3 - Availibility

Option 4 - Availibility

Option 5 - Availibility

Option 6 - Availibility

Option 7 - Availibility

Option 1 - Purchase

Option 2 - Purchase

Option 3 - Purchase

Option 4 - Purchase

Option 5 - Purchase

Option 6 - Purchase

Option 7 - Purchase

[camargos88]

Hi @Anonymous ,

Try this m code:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcixLzMzJTALikkqlWJ1oJf+Cksz8PAVDMEcByjNCljJG5pggc0yROWbIHHMwJ6C0KDkjsTiVthbFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Column1", Text.Trim, type text}}),
Distinct = List.Distinct(#"Trimmed Text"[Column1]),
Option = List.Select(Distinct, each Text.StartsWith(_, "Option")),
Others = List.Union(List.Transform(List.Difference(Distinct, Option),
(x) => List.Transform(Option, each _ & " - " & x))),
#"Converted to Table" = Table.FromList(Others, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"

[Anonymous]

Something like this  Cama

[PI] Question
Option 1   - [PI]
Option 2  - [PI]
Option 3  - [PI]
Option 4  - [PI]
Option 5  - [PI]
Option 6  - [PI]
Option 7  - [PI]
[BRI] Question
Option 1  - [BRI]
Option 2  - [BRI]
Option 3  - [BRI]
Option 4  - [BRI]
Option 5  - [BRI]
Option 6  - [BRI]
Option 7  - [BRI]

[camargos88 · Accepted solution]

@Anonymous ,

Quick update based on this value:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7VZNbxpBDP0r1h4RImrTDykXpIZDOKRQQGol4GB2vTDS7MxmPqD8+9q70DQSLElEcyhFSLDz/Oyx137ydJpMh/05fIvkg7ImaSf8HQcX0xAdJfP2NLmzG8ioJJOhCaA8hBVBao2vrJgEgbAA+WXA2zxs0BFYx+fpylhtl1vI+dHHNCXv86ih1GiMMktAkwH9pDSKo+7MzcyICioW5Nrijv1wQDRgyyqSuJklA0FmSQf4arQWU5XD1kZIV9Z6YiKTakabQxHyWensWmUEyLkEVJokrlwDxawjkYe15W8nBAuuyj70grTddA4VaFAbvDuEwQ5830C8bsA+NGAfGfhxDPzUQPx8CJt+GZ1ohD/yPBr2b+V5zjTHl5Bmr38izTvkwdK66nNHD1E5HokMA3KnkwEygeREBnSNWjHCT8oE2zDmXdjNkFY8OFiWWqW40FT7VWYnHUXB3v0VaVxYV82fv8IsU/IPdW1cWo7ld1P37+nCGfroaHOet5EmlzEvw+Hts9N8bZZHy/OGafYu4m1OTrzMVqvVL0rrgqw0X22gG5iIODzsOKIuoinD3v24ksDb/b4zVsWj4CliPTF6y+5EKCqxEMY+9A18X+HjzhSdY+ETAWTxCWR4GQKbV1hFu+7Bvc2INRmXwEKrAhVdAHELr/kMRkJ9cvUX3qy+R2bTKJINBUu6bG185mMp9XtSmO6+CP+1+s27fv4L", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column3 = _t, Type = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column3", type text}, {"Type", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.StartsWith([Column1], "Option") or Text.StartsWith([Column1], "[")then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns", "Custom", each if Text.StartsWith([Column1], "[") then [Column1] else null),
#"Filled Down" = Table.FillDown(#"Added Custom1",{"Custom"}),
#"Added Custom2" = Table.AddColumn(#"Filled Down", "Custom.1", each if Text.StartsWith([Column1], "[") then [Column1] else [Column1] & " - " & Text.Split([Custom], " "){0})
in
#"Added Custom2"