Sorry but I am not sure if I am able to understand it correctly. My understanding is in powerquery, if I run the query steps again from step, it overwrites the previously stored data.
Usually in first line, I query a data source, is the structure of table returned at first run, preseverd across refreshes? So to say, if I run the query today, I get a table with five columns and some name, is the same structure referenced, when I run the query from start in my next referesh?
I am also sharing my code for reference: I have an api url which I hit in the first step it and returns a json data structure. I convert those records into table. As this json is nested, I expand those records into columns and similarly lists to new rows and finally combine into one table. As you can see I have not hard coded any column name, rather I try to remove them based on a condition. These query steps work fine in powerBI desktop but on powerBI service they fail on next refresh.
let
contents = Web.Contents("url", [Timeout=#duration(0,0,10,0)]),
source = Json.Document(contents),
issues = source[issues],
tble = Table.FromRecords(issues),
//expand list column function
expandListColumn = (Table, ColumnName) => Table.ExpandListColumn(
Table, ColumnName
),
//expand record column
expandRecordColumn = (Table, ColumnName) => Table.ExpandRecordColumn(
Table,
ColumnName,
Record.FieldNames(
List.Select(
Table.Column(Table, ColumnName),
each Type.Is(Value.Type(_), type record)
){0}),
List.Accumulate(
Record.FieldNames(
List.Select(
Table.Column(Table, ColumnName),
each Type.Is(Value.Type(_), type record)
){0}),
{},
(s, c) => List.Combine({s, {Text.Combine({ColumnName, ".", c})}})
)
),
//check for if the column is of type list or record
listOrRecordColumnNames = (Table) => List.Accumulate(
Table.ColumnNames(Table),
{},
(s, c) => s & (
if List.MatchesAny(
Table.Column(Table, c),
each Type.Is(Value.Type(_), type record) or Type.Is(Value.Type(_), type list)
)
then {c}
else {}
)
),
//recursively expand columns
expandColumn = (Table) => List.Accumulate(
listOrRecordColumnNames(Table),
Table,
(state, current) => if List.MatchesAny(
Table.Column(Table, current),
each Type.Is(Value.Type(_), type record)
) then expandRecordColumn(state, current)
else expandListColumn(state, current)
),
expandColumnsRecursive = (Table) => (
if List.IsEmpty(
listOrRecordColumnNames(Table)
)
then Table
else
let
tempTable = expandColumn(Table),
NextTable = @expandColumnsRecursive(tempTable)
in
NextTable
),
expandedTable = expandColumnsRecursive(tble),
//remove null columns
removeNullColumns = Table.SelectColumns(
expandedTable,
List.Select(
Table.ColumnNames(expandedTable),
each List.MatchesAny(
Table.Column(expandedTable, _),
each _ <> null
)
)
),
//remove https column
removeHttpsColumns = Table.SelectColumns(
removeNullColumns,
List.Select(
Table.ColumnNames(removeNullColumns),
each List.MatchesAny(
Table.Column(removeNullColumns, _),
each not (Text.StartsWith(Text.From(_), "https://"))
)
)
)
in
removeHttpsColumns
