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.
Why do we need the filtering for Operation B? Can we just group by primary ID with operation all rows then change each _ to each Table.FirstN(Table.FillUp(_, Table.ColumnNames(_)),1).
What does each record look like? Is there a way to clean it up and combine it so it does not expand so awkwardly into separate lines? Is there some way to Group By ID and Record.Combine(_[parameter column) ? Something like below:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclRQ0lEyNLM01zXUNTAwAXKiFQIMFWwVYpQCMnPyS2KUFGKVYnVwKDQCKjSBKnAi0iBs6oyB6syA0rEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Oper." = _t, #"Primary ID" = _t, #"Pre-Expansion parameters" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Oper.", type text}, {"Primary ID", type text}, {"Pre-Expansion parameters", type text}}),
transform = Table.TransformColumns(#"Changed Type", {"Pre-Expansion parameters", each Expression.Evaluate(_)}), // just converting my example to records
#"Grouped Rows" = Table.Group(transform, {"Primary ID"}, {{"parameters", each Record.Combine(_[#"Pre-Expansion parameters"])}})
in
#"Grouped Rows"
I must apologize as I don't know how to implement your suggestion. What we were given by the data host company is as follows (which may not be optimized):
BasicAuth parameter as "3t54ht343h5h"
lastNDays parameter as N
getData as follows:
(page as number) =>
let
authUrl = "https://app.xyz.com/api/authorization",
endpointUrl = "https://app.xyz.com/api/productions",
random1= Number.Random(),
startTime =Number.ToText (Date.Year (Date.AddDays (DateTime.FixedLocalNow(),-lastNDays))) & "-" & Number.ToText (Date.Month (Date.AddDays (DateTime.FixedLocalNow(),-lastNDays))) & "-" & Number.ToText (Date.Day (Date.AddDays (DateTime.FixedLocalNow(),-lastNDays))),
Source = Json.Document(Web.Contents(authUrl,
[Headers= [ #"Content-Type" = "application/json",
Authorization = BasicAuth, #"Accept" = "*/*" ],
Content = Json.FromValue([scopes = {"productions_write"}, random=random1])])),
TokenTable= Record.ToTable(Source),
accessToken= #"TokenTable"{2}[Value],Origin = Json.Document(Web.Contents(endpointUrl,
[Query= [access_token=accessToken, #"start-time-after" = startTime, page= Number.ToText(page) ],
Headers= [access_Token = accessToken, #"start-time-after" = startTime, page = Number.ToText(page) ]]))in
Origin
And then the start of each query is this:
let
allProductionRecords = List.Generate(()=> [Result = if List.IsEmpty(getProdData(1)) then null else
getData(1), page=1],
each [Result]<>null,
each [Result = if List.IsEmpty(getData([page]+1)) then null else
getData([page]+1),page=[page]+1],
each [Result]),
#"Converted to Table" = Table.FromList(allProductionRecords, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
etc.Can we change this part of your list generate and check??
each [Result]<>null, each [Result = if List.IsEmpty(getData([page]+1)) then null else getData([page]+1),page=[page]+1] into each List.IsEmpty([Result]) = false, each [Result = getData(page),page=[page]+1]Next, if it is still then we need to see if we can transform the list of [Result] so that it does not expand into such a strange format. It would help to see how some of the [Result] look like.