Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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-1 | Americas,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:
Americas East Asia European Union Middle East North Africa South Asia Sub Saharan Africa United Kingdom Wider Europe |
Solved! Go to Solution.
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.
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
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.
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
@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.
Proud to be a Super User!
@kardere -
Try splitting the values and expanding the rows like:
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:
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |