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