cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

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

21 REPLIES 21
Highlighted
Super User IV
Super User IV

Re: CSV values

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

 

split.PNG






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Regular Visitor

Re: CSV values

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
Highlighted
Responsive Resident
Responsive Resident

Re: CSV values

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:

Capture.PNG

 

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"

 

Highlighted
Regular Visitor

Re: CSV values

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

Highlighted
Regular Visitor

Re: CSV values

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

Highlighted
Super User IV
Super User IV

Re: CSV values

@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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Responsive Resident
Responsive Resident

Re: CSV values

Which columns did you have selected when you tried to unpivot?

 

It should look like this before you do the unpivot:

 

before unpivot.PNG

 

Make sure all three of the "Column Values" columns are selected and then Right click on one of the headers and select "Unpivot Columns"

Highlighted
Responsive Resident
Responsive Resident

Re: CSV values

Or...You can right click the "#"/Requests column, and select the "Unpivot Other Colu,mns" option. Same result.

Highlighted
Regular Visitor

Re: CSV values

Hi Dramus,

 

Thanks for helping me out here. Just to be sure I need to split them to columns right then unpivot the columns the all new columns?

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors