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].
I tried the Table.Buffer idea, although that wasn't easy since the documentation is a bit sparse. I think it saved a tiny bit. I haven't tried the unpivot/pivot solution as I can't see a clean way to do that.
When the data comes in, prior to expansion it looks like this:
| Oper. | Primary ID | Pre-Expansion parameters |
| A | 1697-1-004 | [Record] |
| A | 1697-1-004 | [Record] |
| B | 1697-1-004 | [Record] |
| B | 1697-1-004 | [Record] |
| C | 1697-1-004 | [Record] |
| C | 1697-1-004 | [Record] |
After expansion, it looks like this:
| Oper. | Primary ID | P1 | P2 | P3 | P4 | P5 | P6 | P7 | P8 |
| A | 1697-1-004 | Pilot | null | null | null | null | null | null | null |
| A | 1697-1-004 | null | 4 | null | null | null | null | null | null |
| B | 1697-1-004 | Pilot | null | null | null | null | null | null | null |
| B | 1697-1-004 | null | null | null | null | null | null | 0/5/2/0 | null |
| B | 1697-1-004 | null | null | null | null | null | null | null | 6 |
| C | 1697-1-004 | null | null | 6 | null | null | null | null | null |
| C | 1697-1-004 | null | null | null | 20 | null | null | null | null |
| C | 1697-1-004 | null | null | null | null | 6 | null | null | null |
| C | 1697-1-004 | null | null | null | null | null | 20 | null | null |
Note that many are delimited, so my number of columns grows quickly too. Note that after expansion, each parameter row only has one useful bit of information.
My current solution is to bring all of this into one query (Process_Table). I then create a new query using this:
Source = Table.SelectRows(Process_Table, each ([Operation] = "B"))
followed by removal of a bunch of columns. I can then 'group by' and remove the nulls without much problem. Again, not sure this is the best way, with "best" meaning quickest to process.