Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 valu...
Need Help
  • camargos88's avatar
    camargos88
    6 years ago

    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"

     

camargos88's avatar
camargos88
Community Champion
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      camargos88
      Community Champion
      6 years ago

      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"