Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi,
Im trying to separate metadata (tags) in PowerQuery, and am somewhat successful but cant get it all they way when it comes to 1 scenario.
In my data I have one column that containts metadata / Dimensions separated by ";".
Dimension 1 (Company name) = FMC Buses, FMC Cars etc
Dimension 2 (Country) = Germany, Spain, US etc
Example of a tags: "FMC Trucks; Germany;FMC Cars;FMC Buses"
Example 1: "FMC Trucks; Germany;FMC Buses"
Example 2: "FMC Trucks; India;FMC Cars"
This is what I have today:
List.First(List.Select([TagsSplit], each
Text.Contains(_,"FMC Trucks") or
Text.Contains(_,"FMC Cars") or
Text.Contains(_,"FMC Buses") )
That cherrypicks the ONE of the Companies.
The problem:
The dataset consists of products, and a product could have multiple tags.
Example: FMC Trucks; FMC Cars; Germany; China
What I want to achieve:
Example:
Product A
Tags: FMC Cars; FMC Buses; Germany
I want to be able to sort by either one of these dimensions above and get the same product no matter if I filter on "FMC Cars" and/or FMC Buses and FMC Cars.
@v-cgao-msft
Thank you for the response and help!!
How do I get to a situation where I can have a KPI (e.g. a Gauge or something) for example:
FMC Trucks = 8
FMC Buses = 5
With the totals above, the total of 8 (Trucks), includes the products that also are a part of "Buses" ?
Same principle for the countries etc.
Hi @tonijj
If a record is a product, you can add an index column to mark the product and then split it like this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvN1VggpKk3OLrZWcE8tyk3Mq7QGiTknFhWDGU6lxanFSrE6uJViV+GZl5KZCDcKQxpuB9woBeeMzLxEpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [tags = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"tags", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "TagsSplit", each List.Intersect( { {"FMC Trucks", "FMC Cars","FMC Buses"}, Text.Split([tags],";")})),
#"Expanded TagsSplit" = Table.ExpandListColumn(#"Added Custom", "TagsSplit")
in
#"Expanded TagsSplit"
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum -- China Power BI User Group
@v-cgao-msft Thank you for the response and help!!
How do I get to a situation where I can have a KPI (e.g. a Gauge or something) for example:
FMC Trucks = 8
FMC Buses = 5
With the totals above, the total of 8 (Trucks), includes the products that also are a part of "Buses" ?
Same principle for the countries etc.