Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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"
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"
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]
@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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |