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

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