cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kardere
New Member

Use slicer to filter a column which as several values separate

Hello ALL,

 

I have a product table which has Regions cloumn. A product can have many regions. The region column is a choise and multiple selection. That means user can chose many regions at once for one product.

When I import this SharePoint list into the Power BI, then region column shows several values separated by comma. Such as:

Product-1Americas,East Asia,European Union, etc.

 

I want to show/filter products by a region using the slicer in Power BI. I chose the slicer and add Regions column and it doesn't not region one by one, instead it shows several of them by comma and doesn't not filter properly.

I was wondering if there is any sloution for this.

Looking forward to hearing from you.

Thanks in advanced.

Regions.png

Regions:

Americas
East Asia
European Union
Middle East North Africa
South Asia
Sub Saharan Africa
United Kingdom
Wider Europe
1 ACCEPTED SOLUTION
yingyinr
Community Support
Community Support

Hi @kardere ,

I created a sample pbix file (see attachment) for you, it split the regions and unpivot the related region columns by using Power Query Editor. Please check whether that is what you want.

yingyinr_0-1620982171426.png

yingyinr_1-1620982264468.png

And if you want to split the regions by Power Automate, you can refer the following video to get it.

Split a String Variable in Power Automate (MS Flow)

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
yingyinr
Community Support
Community Support

Hi @kardere ,

I created a sample pbix file (see attachment) for you, it split the regions and unpivot the related region columns by using Power Query Editor. Please check whether that is what you want.

yingyinr_0-1620982171426.png

yingyinr_1-1620982264468.png

And if you want to split the regions by Power Automate, you can refer the following video to get it.

Split a String Variable in Power Automate (MS Flow)

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

ChrisMendoza
Super User I
Super User I


@kardere wrote:

Do I need to go into the Power BI, to do the split column by delimiter every time someone add a new data into the SharePoint list? Or Once I have done the split column process then it will always stay the same.
Lastly, how did you create that script/source and where this script should be inserted and is there any way to make this process automated by Power Automate/flow.

 I used Power Query to split the column, it's Power Automate like I suppose without the 'triggers'. PQ is the data transformation editor your SP List goes through to get to Power BI. Hope that helps.






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



ChrisMendoza
Super User I
Super User I

@kardere -

Try splitting the values and expanding the rows like:

image.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s9LVdJRcsxNLcpMTizWcU0sLlFwLM5M1HEtLcovSE3MUwjNy8zPU4qNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Region = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Region", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Region", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Region"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Region", type text}})
in
    #"Changed Type1"

Using the interface:

image.png






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Thanks @ChrisMendoza for your quick response. It is very helpfull.

 

This data list is in a SharePoint list. It is connected to Power BI. People regularly updating the list. Also, people regularly get information from this Power BI reports.
My question is this:
Do I need to go into the Power BI, to do the split column by delimiter every time someone add a new data into the SharePoint list? Or Once I have done the split column process then it will always stay the same.
Lastly, how did you create that script/source and where this script should be inserted and is there any way to make this process automated by Power Automate/flow.
Kind Regards

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors