table not rendering any content

[Anonymous]

I have a 2-column table that renders fast & fine in the desktop app, but not in the service, where it seems to be stuck refreshing much longer than other tables, but then simply doesn't show up at all. I deleted the table, re-created it in desktop, published again, but to  no avail. What should I try next?

[v-shex-msft]

Hi @Anonymous ,

Please provide more detail information or help us clarify your senior.(e.g. datasource type, data amount, connection mode, advanced in query editor, snapshot...)

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

[Anonymous]

@v-shex-msft  thanks, data source are two tables in .xlsx files in a SharePoint folder, 40k rows (4MB) in the facts table and and 6k rows (150KB) in the dimension table. There is a  many to one relationship, cross filtered in both directions. Connection is to SharePoint folder, data is in the cloud (no gateway required). Was working fine until a few weeks ago, there have been no changes to underlying data structure nor to the .PBIX report.

That's how it shows in PowerBI desktop:

In the service the table doesn't come up. It keeps refreshing for a few minutes, then seems to time out with no error, simply remains blank:

dimension table query

let
    Source = SharePoint.Files("https://ourcompany.sharepoint.com/sites/OECI/", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "https://ourcompany.sharepoint.com/sites/OECI/Cognos/") and ([Name] = "Yantra Backlog.xlsx")),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Rows", "Transform File from Query3", each #"Transform File from Query3"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Query3"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Query3", Table.ColumnNames(#"Transform File from Query3"(#"Sample File (4)")))
in
    #"Expanded Table Column1"

facts table query

let
    Source = SharePoint.Files("https://ourcompany.sharepoint.com/sites/OECI/", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "https://ourcompany.sharepoint.com/sites/OECI/Cognos/") and ([Name] = "Yantra G11.xlsx")),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Rows", "Transform File from Query1", each #"Transform File from Query1"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Query1"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Query1", Table.ColumnNames(#"Transform File from Query1"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Order No", type text}, {"BU", type text}, {"Order Date", type date}, {"Order Type", type text}, {"Work Order No", type text}, {"Description", type text}, {"Queue Name", type text}, {"User Name", type text}, {"Active Flag", type text}, {"Closed On", type datetime}, {"Installation Complete", type datetime}, {"Current Status", type text}, {"Current Status Date", type datetime}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Order Date", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "TTR", each [Installation Complete] - [Order Date]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"TTR", type duration}})
in
    #"Changed Type2"

Everything works fine in the desktop app, but the table displaying the count for each scope will not render in the service. Everything else works fine in the service, too, except this particular table (which is nothing special).

[Anonymous]

@v-shex-msftI did some more testing and found one interesting fact:

If I set a filter 'Scope is not (Blank)', the table shows values in the Service.

If I include blanks, the table only shows values in the desktop app, not in the Service.

What could be causing this? Data is in SharePoint online (not on premises, no gateway).

[v-shex-msft]

HI @Anonymous ,

I think these should records to your records and relationships, please check your table records to confirm if they had correspond records existed on both two tables.
It seems like 'one : one' relationship can't correct mapping two table records so that most of records are mapping on 'blank' records.

Regards,

Xiaoxin Sheng

[Anonymous]

@v-shex-msft  the relationship is not 1 to 1 but many to one and is perfectly fine, there are a few rows where scope is blank because there is no matching record in the other table, and I want to show them because it tells me there are tickets in queues that are neither in nor out of scope! This works in the desktop app, the table show there are 7 items with no matching record:

Isn't that just a simple left outer join? Why does this table not render in the Service if I don't filter out blanks?

[v-shex-msft]

Hi @Anonymous ,

Can you please share a sample pbix file to test? It is hard to troubleshoot and reproduce your scenario without any detail sample data.
Notice: do mask on sensitive data before share.

Regards,
Xiaoxin Sheng