Forum Discussion

SuraMan's avatar
SuraMan
Advocate II
7 years ago
Solved

nested oData, XML, JSON data in Power BI model?

This question applies to consuming XML or JSON data in Power BI.   oData services can provide nested data as Lists, Records, and Tables.   An example is that a Product is associated with two C...
Need Help
Show and Tell
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi SuraMan 

    Transform these to UI,

    let
    Source = Products,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"ID", "Categories"}),
    #"Expanded Categories" = Table.ExpandTableColumn(#"Removed Other Columns", "Categories", {"ID", "Name", "Products"}, {"Categories.ID", "Categories.Name", "Categories.Products"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Expanded Categories",{"ID", "Categories.ID"})
in
    #"Removed Other Columns"

     In Edit queries, i create a blank query,

     

    #"Removed Other Columns"

     

    Best Regards

    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

Hi SuraMan 

You could create a link table to connect each category with these products, then create correct relationships for "category" and "product" tables.

In Edit queries, i create a blank query, paste the code below in Advanced editor,

let
    Source = Products,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"ID", "Categories"}),
    #"Expanded Categories" = Table.ExpandTableColumn(#"Removed Other Columns", "Categories", {"ID", "Name", "Products"}, {"Categories.ID", "Categories.Name", "Categories.Products"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Expanded Categories",{"ID", "Categories.ID"})
in
    #"Removed Other Columns1"

Close&&apply,

Create relationships

Please doanload my pbix to see more details.

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • SuraMan's avatar
    SuraMan
    Advocate II
    7 years ago

    thanks v-juanli-msft for your detailed response.

     

    I can see that you wrote an M query to fetch data from Products and the transform it to a link table. Can this be done via Power BI UI?

     

    • v-juanli-msft's avatar
      v-juanli-msft
      Community Support
      7 years ago

      Hi SuraMan 

      Transform these to UI,

      let
    Source = Products,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"ID", "Categories"}),
    #"Expanded Categories" = Table.ExpandTableColumn(#"Removed Other Columns", "Categories", {"ID", "Name", "Products"}, {"Categories.ID", "Categories.Name", "Categories.Products"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Expanded Categories",{"ID", "Categories.ID"})
in
    #"Removed Other Columns"

       In Edit queries, i create a blank query,

       

      #"Removed Other Columns"

       

      Best Regards

      Maggie

       

      Community Support Team _ Maggie Li
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • SuraMan's avatar
        SuraMan
        Advocate II
        7 years ago

        Thank you Maggie for your detailed assistance.