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