Forum Discussion

TootsiePatootsi's avatar
TootsiePatootsi
Regular Visitor
8 years ago

CSV values

Hi,

 

First of all thanks for reading this. I am new to PowerBI so I am hoping you could help me out.  I have the following data

#Column Values
Request #1A,B,C
Request #2C,A


As you can see in the table above my column values is seperated  by a comma. What I wanted to achieve in my report is this.

PowerBICount
Selection A2
Selection B1
Selection C2


I already tried the "Split column" features usinga delimiter, but when I use split into columns and use it in my report it did look great because somehow it looks like that data is not merging. When I tried to split into rows, it also affect my other column and duplicates it.

Thanks

Need Help

21 Replies

  • parry2k's avatar
    parry2k
    Super User
    8 years ago

    Make sure to split it in rows and then you can get what you are looking for:

     

    • TootsiePatootsi's avatar
      TootsiePatootsi
      Regular Visitor
      8 years ago

      Hi,

       

      Thanks for the reply. I tried it out but somehow other data was also duplicated


      #ColumnOther Columns
      Request #1A,B,CValue 1
      Request #2C,AValues 2

       

       

      After splitting it my table turns into this which affects my other report:

      #ColumnOther Columns
      Request #1AValue 1
      Request #1BValue 1
      Request #1CValue 1
      Request #2CValues 2
      Request #2AValues 2
  • dramus's avatar
    dramus
    Continued Contributor
    8 years ago

    Split and Unpivot is your friend.

     

    First split your "Column values" using a comma as the delimiter.

    Then select the columns you just created and select unpivot.

    Finally delete the "Attribute" column that was created.

    You will end up with something that looks like this:

     

    Here's the Power Query

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkotLE0tLlFQNlTSUXLUcdJxVorVQRI2Ago76zgqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Requests = _t, #"Column Values" = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column Values", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column Values.1", "Column Values.2", "Column Values.3"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Requests", type text}, {"Column Values.1", type text}, {"Column Values.2", type text}, {"Column Values.3", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Requests"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})
in
    #"Removed Columns"

     

    • TootsiePatootsi's avatar
      TootsiePatootsi
      Regular Visitor
      8 years ago

      Hi,

      Thanks for the reply. I am not familiar with PowerQuery yet and also Unpivot ill see if this could help and let you know

    • TootsiePatootsi's avatar
      TootsiePatootsi
      Regular Visitor
      8 years ago

      Hi Dramus,

       

      I tried the unpivot feature but somehow all my columns shows error and showing this error

       

      We couldn't parse OData response result.  Error: A value without a type name was found and no expected type is available. When the model is specified, each value in the payload must have a type which can be either specified in the payload, explicitly by the caller or implicitly inferred from the parent value

       

      Thanks in advance

      • parry2k's avatar
        parry2k
        Super User
        8 years ago

        TootsiePatootsi columns will be duplicate once your split it to rows and that is the only way you can get what you are

        looking for.