Forum Discussion

tonijj's avatar
tonijj
Helper IV
2 years ago

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. 

 

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      tonijj
      Helper IV
      2 years ago

      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.  

  • tonijj's avatar
    tonijj
    Helper IV
    2 years ago

    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.