Web.Content Custom Connector Folding

[LiranRom]

Hello,

We have a connector that gets data from our server, and have RangeStart and RangeEnd and pagination support - All works great.

But, one of the column in the data has value which is a list of objects that are in this structure: [{"key": "column 1", "value": "value 1"}, {"key": "column 2", "value": "value 2"]
and we wish to flatten this list of object based on the keys.

each key - is a column. each value - will be its value in that row.

in the code - I've Pivoted this column successfully. and on Power BI Desktop - all works great.

but for each rows it can have different values for the key - so the columns at each page can be different.

so this is a legal options:

[{ "ID": 1, list: [{"key": "column 1", "value": "value 1"}, {"key": "column 2", "value": "value 2"]}, ... (several pages)

{ "ID": 2000 list: [{"key": "column 1", "value": "value 2"}, {"key": "column 3", "value": "value 3"]}, .... (several pages)

{ "ID": 330000, list: [{"key": "column 4", "value": "value 34"}, {"key": "column 35", "value": "value 46"]}]

after Pivot in code I got:

ID	column 1	column 2	column 3	column 4	column 35
1	value 1	value 2
2	value 2		value 3
3				value 34	value 46

there could be many pages, many rows, severals columns names for each object (not only 2) could be none, and values can be nullables 

as a result in the power desktop - when I refresh the model - the keys displays as columns - works great.
But- when I tried to set Incremental Refresh and publish it to Power BI Cloud - on first page there was an error say something like "The 'column 35' column does not exist in the rowset."

I guess this is something relate to the Folding requirement of the Incremental Refresh.
because Columns in the first page can be different then in the second one or in the page number 100... and the Power BI Incremental Refresh mechanism doesn't just "Add column" and append the data according to the name.
in Power BI Desktop it does.

What can I do? how can I Pivot the list in the connector code so our customer will easier get the data as a regular column and not need to make thier own manipulation like: Pivoting / Expanding ?

Please Advice thanks,

Liran

[LiranRom]

Thanks @lbendlin for your feedback

Just to clearify what you say...

lets takes this structure for an example:

for the first page I have table with ID, Column1, Column2

but when I pulled page #2 I got table with ID, Column2, Column3

is this is something that Incremental Refresh will "know" how to handle - by append Column2 data, Add new Column3 and append thits data, and not remove Column1?

and what about if on page #3 I got table with ID, Column4, Column35

will it Add Column4 and Column35 to existing table?

the question is: what is the logic behined Incremental Refresh?

Is it: "when get next table - find if there is already Column with same name - if so - then append accordingly, and if it's not exist, Add new Column. And never remove Columns"?

Another question: 

Is the first row for each page - must be filled with data under all table columns? 

so if I have page #2 (on same example) that contains: ID, Column2, Column3 but on first row Column2 is empty

it will lead to faliure? 

so I need to make sure the first row of any page - start with data for all Columns on that page

or should it be enough that I got page with columns: ID, Column5, Column6, Column7

and the rows will be like that:

row #1 - has data on Column5 (no data on others)

row #2 - has data on Column6 (no data on others)

row #3 - has data on Column7 (no data on others)

what will be the result then? will it lead to error?

Thanks very much for your help,

I'm tryig to be clear with specific examples so I will understand the answer and know what should I do to avoid such errors

[lbendlin]

Unpivot your data into key/value columns.

[LiranRom]

the Pivot is the way I use for Expand the list of objects, into flatten columns in the source table.

UnPivot - is not the answer for my issue.

I do need to Expand the list - into flatten columns.

and the question what is the Inremental Refresh limit that prevent me to do that

[lbendlin]

where is the DateTime column that you are planning to use for the partition boundaries?

[LiranRom]

one of the table columns is a DateTime column (meaning: in any row there is ID, datetime, and more columns..)
therefore the partition and Incremental Refresh works great when the columns are the same for all rows.
the problem start only when I started to Expand the list key,value column into dynamic columns ..

[lbendlin]

Add a fake row to each table that has all the columns you will ever see in the individual sources.

Or, do the right thing and unpivot your data.

[LiranRom]

I have specific request - I need to Expant a list into a flatten table columns. the pivot is the only way Power Query allows that. as far as I familiar with.

so I understand that the limitation is - that the first row of each page must be with values on all its columns.

ok

[lbendlin]

You don't need to have a pivoted result in Power Query. You can let the visuals do the pivoting for you.

[LiranRom]

but I want our customers to get out of the box (on Get Data) - a table with flatten columns, without list of object data. I don't want each customer to perform pivoting steps by themselves.

[lbendlin]

because Columns in the first page can be different then in the second one or in the page number 100

That sounds more like an Excel style approach.  Keep your source data in the key/value narrow column structure.

While it is possible to combine tables with slighty different column structures, you need to hand craft the queries to remove any absolute column name references.