Metatags - Separate in Query with Multiple dimensions

[tonijj]

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. 

[tonijj]

@Anonymous 

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.  

[Anonymous]

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

[tonijj]

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