Forum Discussion
How to expand record without mostly null values
Maybe try
Table.TransformColumns(Source, {"parameters", Record.Combine})
On the step when parameters is still a list. This will provide cleaner expansion if each record does not have fields with null values. I am hoping the nulls just come because you have a bunch of different records with different field names and when expanding the records at the same time you get nulls because the fields don't exist in other records. When you click on an invidual cell you can see what the record looks like below.
If records that are being combined have the same field, it will be overwritten by the latest value.
Combining [A = 1] with [A = null, B = 2] will give you [A = null, B = 2].
Combining [A = 1] with [B = 2] will give you [A = 1, B = 2].
Here are the applied steps for one of my queries:
In my file I have 13 queries, and most of them are at least similar to the above. In essense, pull in data from API, expand and choose what I want, then do necessary operations. Unfortuneately, the 'parameters' steps is what causes the stair-stepped null value problem, and it's where I have most information. What I'm doing is pulling in all process data that is collected in our manufacturing system (API) and then graphing, analyzing, etc. The company that hosts our data is not very adept at PBI as well, so I'm sure there's some inefficiencies in there too.
I've noticed that filtering takes forever, and so does grouping. Most of the excess time is waiting for API, so it could be more of an API issue than PBI, that's why I'm trying to move as much as I can into the DAX area. I'm a bit of a novice here so I've rebuilt my file from scratch a few times now to keep improving. Over the holidays is rebuild #4 I believe.
Can you please provide some sample data to work with that has a similar structure to what you are working with? You can keep you company data protected by changing it to something that can be shared. For example, click on the Filtered Rows1 step, copy the preview data and paste it in Excel, then change the values so that they are content that can be shared. Then paste the table into a message. Do the same thing with the parameter values so we can see what the structure of those are. Please include an explanation of how the parameter values relate to the previous step so we can sufficiently help. Thanks!
Try putting a Table.Buffer around your first step (or the first step that is a Table). Maybe your query keeps repeating the API call and this can prevent that. If it helps then keep the Table.Buffer. If your first step is a list maybe try a List.Buffer.
This stepped behavior on expansion usually occurs when a couple of columns should be combined prior to expansion and/or combined and then unpivoted. That's why it will help to see some specific examples.
