Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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.
@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"
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.
@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"
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.