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
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
v-yiruan-msft
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
v-yiruan-msft
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.
ChrisMendoza
Resident Rockstar
Resident Rockstar


@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
Resident Rockstar
Resident Rockstar

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