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

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

Reply
tonijj
Helper IV
Helper IV

Metatags - Separate in Query with Multiple dimensions

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: 

  • Being able to display Total of products per dimension
  • Being able to show a product with several dimensions without creating duplicates

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. 

 

 

3 REPLIES 3
tonijj
Helper IV
Helper IV

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

v-cgao-msft
Community Support
Community Support

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"

vcgaomsft_0-1716961959005.png

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.  

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Top Solution Authors
Top Kudoed Authors