Table inside Table in columns

[Anonymous]

I am importing data from CRM Dynamics through api link. When i am selecting the tables and expanding it i can seem many columns with tables in the rows again. I am not sure how do i select required columns from the tables in the rows of the columns.. 

Fairly new to this and need resolution. 

[mahoneypat]

If it helps, fyi that you can pass filters and select certains columns to be returned by adding paramters to your web call using ?$filter=...&$select=...

You can likely improve performance of your refresh, and you would only see the columns you selected when you go to expand.

Regards,

Pat

[Anonymous]

Hi @mahoneypat ,

I am adapting to your suggestion and will come back to you once i catch up with the numbers. 

Thank you for your response. 

[smpa01]

@AnonymousI can seee that you have used Odata.Feed.

As far as I know, you can utilize scripting inside Odata.Feed to select your columns

https://docs.microsoft.com/en-us/powerquery-m/odata-feed

https://docs.microsoft.com/en-us/azure/devops/report/powerbi/odataquery-connect?view=azure-devops

If you don't succeed there, you need to manually select your column like following

 {"fruits":[
{
kind: "grape",
"color":[{
color1: "red",
color2:"green"
}],
quantity: 12,
tasty: true
},
{
kind: "kiwi",
"color":[{
color1: "brown",
color2:"green"
}],
quantity: 98,
tasty: true
},
{
kind: "mango",
"color":[{
color1: "yellow",
color2:"green"
}],
quantity: 0,
tasty: true
}
]
}

fruits2.json

let
    Source = Json.Document(File.Contents("C:\Users\X\Desktop\fruits2.json")),
    fruits = Source[fruits],
    #"Converted to Table" = Table.FromList(fruits, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"kind", "color", "quantity", "tasty"}, {"kind", "color", "quantity", "tasty"}),
    #"Expanded color" = Table.ExpandListColumn(#"Expanded Column1", "color"),
    #"Expanded color1" = Table.ExpandRecordColumn(#"Expanded color", "color", {"color1"}, {"color1"})
in
    #"Expanded color1"

[Anonymous]

Hi @smpa01 

I went through the article and understood that it will take some more digging into the data base to extract columns needed. This is helping me right now. 

I wanted to know if it is possible to expnad the query with the columns required with out writing the query from tables inside tables. 

Thanks. 

[smpa01]

@Anonymouscan you please provide a snapshot and the sample query. You can expand the desired columns as mentioned in #"Expanded Count"

let
    Source = #table({"First Column", "Second Column"}, {{1,2},{3,4}}),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Index"}, {{"Count", each _, type table [First Column=number, Second Column=number, Index=number]}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"First Column"}, {"First Column"})
in
    #"Expanded Count"

[Anonymous]

Hi @smpa01 ,

Below is the query and screenshot. 

Opportunity table is one table out of 100+ tables. After selecting Opportunity table when i expand it provides me more thatn 375+ columns including columns with tables in their rows. 

I have used power query for dashboard and reports but never worked on this big datasets where i have tables inside table in many columns from where i need few columns only. 

Could you please guide how to normalize this kind of database. It's crm data from server thorough api link. 

let
Source = OData.Feed("https://XXXXXX.api.crm.dynamics.com/api/***/", null, [Implementation="2.0"]),
opportunities_table = Source{[Name="opportunities",Signature="table"]}[Data]
in
opportunities_table

Here i am ge