Forum Discussion

JLYT's avatar
JLYT
Frequent Visitor
2 years ago
Solved

Help with transforming data

Hi,

 

We are using Mongo Atlas as the database in our organisation which has a tag array and I am struggling to create a column for each tag key on Power BI.

 

Tables in Power Query appears like this:
content

content_idtag_id
1123
1234
1456
2234
2456

tag

idkeyvalue
123TypeVideo
234TypeAudio
456TopicHealth and Safety


I would like to have the table visual as:

content_idTypeTopic
1Video, AudioHealth and Safety
2AudioHealth and Safety


I would also like to be able to filter by each tag parameter. So for example, I need a slicer to say type = video. Then it will filter the table to show only content_id = 1.

 

Hope this makes sense!

 

Thanks

Jennifer

Need Help
  • DataInsights's avatar
    DataInsights
    2 years ago

    JLYT,

     

    Try this in Power Query:

     

    let
    Source = Table.NestedJoin(content, {"tag_id"}, tag, {"id"}, "tag", JoinKind.LeftOuter),
    ExpandTag = Table.ExpandTableColumn(Source, "tag", {"key", "value"}, {"tag.key", "tag.value"}),
    RemoveTagId = Table.RemoveColumns(ExpandTag,{"tag_id"}),
    ConcatValue = Table.Group(RemoveTagId, {"content_id", "tag.key"}, {{"tag.value", each Text.Combine([tag.value], ", "), type text}}),
    PivotKey = Table.Pivot(ConcatValue, List.Distinct(ConcatValue[tag.key]), "tag.key", "tag.value")
in
    PivotKey

     

     

    Regarding filtering a visual by video (for example), use the DAX function CONTAINSSTRING.

2 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    2 years ago

    JLYT,

     

    Try this in Power Query:

     

    let
    Source = Table.NestedJoin(content, {"tag_id"}, tag, {"id"}, "tag", JoinKind.LeftOuter),
    ExpandTag = Table.ExpandTableColumn(Source, "tag", {"key", "value"}, {"tag.key", "tag.value"}),
    RemoveTagId = Table.RemoveColumns(ExpandTag,{"tag_id"}),
    ConcatValue = Table.Group(RemoveTagId, {"content_id", "tag.key"}, {{"tag.value", each Text.Combine([tag.value], ", "), type text}}),
    PivotKey = Table.Pivot(ConcatValue, List.Distinct(ConcatValue[tag.key]), "tag.key", "tag.value")
in
    PivotKey

     

     

    Regarding filtering a visual by video (for example), use the DAX function CONTAINSSTRING.

    • JLYT's avatar
      JLYT
      Frequent Visitor
      2 years ago

      Oh wow! This is great! Thank you so much!

       

      For anyone else that is having similar issue, I created a blank query with the query above. Then I create a relationship between this new table id column and the original content column so that I can pull other columns in the original content table.

       

      Thank you so much

      Jennifer