Forum Discussion
Tag column values with the header
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 |
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"
3 Replies
camargos88Community Champion
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"- camargos88
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"