Help with transforming data

[JLYT]

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_id	tag_id
1	123
1	234
1	456
2	234
2	456

tag

id	key	value
123	Type	Video
234	Type	Audio
456	Topic	Health and Safety

I would like to have the table visual as:

content_id	Type	Topic
1	Video, Audio	Health and Safety
2	Audio	Health 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

[DataInsights · Accepted solution]

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

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