Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
LiranRom
Helper I
Helper I

Web.Content Custom Connector Folding

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:

 

IDcolumn 1column 2column 3column 4column 35
1value 1value 2   
2value 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

 

 

 

 

 

 

 

 

 

10 REPLIES 10
LiranRom
Helper I
Helper I

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

 

 

 

 

 

 

 

 

Unpivot your data into key/value columns.

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

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

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

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.

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

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

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
Super User
Super User

 

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.

 

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.