Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TootsiePatootsi
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
dramus
Continued Contributor
Continued Contributor

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"

 

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

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"

dramus
Continued Contributor
Continued Contributor

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

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?

Yes. Split and then unpivot.

Thats weird after I unpivot the new columns it 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.

 

Any idea what could be? Note the data was being pulled from sharepoint

If you delete that last step "Value", does the pivot succeed?

Hi Dramus,

 

I was able to solve the issue but its doesn't turn out to be what I was expecting. I use the unpivot but result is somehow the same with splitting a column into multiple rows instead of column. And also I had to do this with other column does it mean each column with CSV on it I had to create a new data source?

@TootsiePatootsiWe are working against the very limited dataset you provided (2 columns, 3 rows). If you can provide something that's closer to your real dataset, we might be able to give you an answer, but with what we have at the moment, I'm not sure that we can reproduce what you are trying to do and give you an exact answer.

 

Can you share (even privately) your PBIX file?

HI Dramus,

 

Sorry but I cannot share the pbix its quite to confidential. I ended up having multiple datasource for each fields that has CSV. Not sure now how to use slicer filter graphs/report that came from different data source

Without knowing more about your model, I'm not sure I can give you any advice on how to make the filters work.

@TootsiePatootsiI mentioned you at the start of the thread, you have to split it to rows and eventually that is what unpivot did.

 

Regarding you have to create it for each column, it all depends what you are trying to do and what is the end goal. For now, solution was provided to get you the answer for your question but if there is something more required, list the full table columns and desired end result, it will help to provide you the best solution.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

Sorry about that I am new to PowerBI I didnt expect that it unpivot will be the same result. I am thinking of having multiple copies of my datasource(referenced to my original one) for each fields with CSV on it. Now since I have multple datasource. Now since I have multiple data source I am not sure how can I make them be filtered by just 1 slicer

Hi dramus,

 

The last step("Value") only shows up when I click on an error. Even I deleted it still does not work.

 

 

error2.jpg

 

 

error1.jpg

Ill try it out, my data is quite large it takes quite a few minutes for the unpivot. Let you know what would be the result

 

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

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

parry2k
Super User
Super User

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

 

split.PNG



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.