Forum Discussion

LiranRom's avatar
LiranRom
Helper I
2 years ago

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

 

 

 

 

 

 

 

 

 

Need Help

10 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

     

    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.

     

     

  • LiranRom's avatar
    LiranRom
    Helper I
    2 years ago

    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

     

     

     

     

     

     

     

     

      • LiranRom's avatar
        LiranRom
        Helper I
        2 years ago

        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