[Power BI][Hubs] An error occurred in the ‘ftk_LoadData’ query. Expression.Error: We cannot convert the value null to type Table.

[jeromecalderwood]

🐛 Problem

When Setting up the FOCUS Power BI Template as I am using the finops hub (it's deployed and running correctly), I am getting an error stating:

An error occurred in the ‘ftk_LoadData’ query. Expression.Error: We cannot convert the value null to type Table.
Details:
Value=
Type=[Type]

The hub has all information populated. Do I also need to populate the CM Connector?

Any Ideas?

[flanakin]

This is a bug, which we'll fix in 0.1.2. As a workaround, you can either specify the Billing Account ID parameter with a valid value or change the CostDetails query to use ftk_LoadData(1). This will tell it to not use the connector to pull actual costs.

[flanakin]

Sorry, I might be confusing this error with another one... This might be caused by the export not getting processed correctly in the Data Factory instance. Let me see if I can repro this and identify the issue...

[jeromecalderwood]

Thank you, I changed the query to no avail.

[flanakin]

@jeromecalderwood Can you check to ensure the ingestion container has data in it?

See https://microsoft.github.io/finops-toolkit/hubs/troubleshoot#ingestion-container-is-empty

I just ran into this error recently and discovered it was about the data not getting exported with file partitioning enabled. If you don't see data in the ingestion container, check the export to see if it was configured with file partitioning.

[jeromecalderwood]

Thanks for this, I have data in there. What I did was I removed all the data, created the export and tried again. The exports work fine. The PowerBi Template FOCUS or the costs one still show the same error as above.

[thecloudman]

I had this error, it was because the ingestion container was empty. My transform function was pushing the data out to a new container, I changed the export to use file partitioning and the transform then put the data into the ingestion container.

[flanakin]

@jeromecalderwood, are you still running into this? We've fixed a few things that will go out in the 0.2 release that might help here depending on what the underlying issue is. Let me know if you're still hitting this and we can hop on a call to walk thru it with 0.2 next week.

I'll move this to 0.2.1 so we don't lose track of it. Looking forward to hearing back.

[psilantropy]

Hi. I received this error today with a fresh deployment on the latest CostSummary and CommitmentDiscounts pbix.
Month to date export for January only. Let me know if you need anything attempted.

We cannot convert the value null to type Table.

An error occurred in the ‘’ query. Expression.Error: We cannot convert the value null to type Table.
Details:
Value=
Type=[Type]

[thecloudman]

@psilantropy does your ingestion container have data in it?

[psilantropy]

@psilantropy does your ingestion container have data in it?

Hi. Yes it does;

ingestion/nameofmydailyexport/amortizedcost/part_0_0001.parquet
also if relevant
msexports/billingid/nameofmydailyexport/20240101-20240131/someguid/manifest.json

Data factory pipelines transform and extract both list succeeded.
Trigger run succeeded
Runtime running

[thecloudman]

@psilantropy does your ingestion container have data in it?

Hi. Yes it does;

ingestion/nameofmydailyexport/amortizedcost/part_0_0001.parquet also if relevant msexports/billingid/nameofmydailyexport/20240101-20240131/someguid/manifest.json

Data factory pipelines transform and extract both list succeeded. Trigger run succeeded Runtime running

@psilantropy Did you do your cost export using file partitioning? Did you use preview export or GA export?

[psilantropy]

I used the preview export as per please use the [Exports preview link](https://aka.ms/exportsv2). File partitioning enabled (unable to change this) on the export job.

[jeromecalderwood]

Hello all, it's been busy. Yes, with the new Power Bi document, I see the above issue, and I have data in spots where it needs to be.

[flanakin]

@jeromecalderwood Can you confirm that you have:

1. Deployed FinOps hubs 0.2
2. Created new FOCUS exports
3. Confirmed the FOCUS exports are getting populated into the ingestion container in the .../focuscost folder
4. Used the Power BI reports from 0.2 to access the hub instance

The ftk_LoadData is only in the old reports, so I just want to be sure we're on the same page.

[BMichaelJ]

Hi @flanakin
Facing the same issue with FOCUS report and ftk_LoadData error.

1. have Deployed FinOpsHubs 0.2
2. Created the Focus Export
3. Repors are dropped in .../focuscost
4. Using the Power BI report from 0.2 (3 month old)

Any advise on troubleshooting?

[flanakin]

0.2 has a couple bugs. Fixes are incoming. Here's a release candidate: https://gist.github.com/flanakin/419b5ed778d12a109b297d0dd406c10f
I did a quick pass to make sure the files land in ingestion. Will confirm the rest later.

[flanakin]

Update: I was able to validate that data is processed in storage and loads in the Cost summary report. Please let me know if you run into any issues.

@jeromecalderwood / @thecloudman / @psilantropy / @BMichaelJ, can each of you confirm if this addresses your issue here?

[thecloudman]

@flanakin I tried to connect PBI to data imported into ingestion last week using a new download of cost summary this morning. I still get this error:
An error occurred in the ‘’ query. DataFormat.Error: We couldn't parse the input provided as a Date value.
Details:
6ed29d88-05fd-47b1-9568-991dc068660420240101

[BMichaelJ]

@flanakin I can confirm it did address the issues and we have a working setup now, thanks!

[arthursilvany]

I tested the focus report with 0.3v using Cost and Usage Focus. The dataset version is 1.0 preview (v1), and it raises the same issue: Value =
Type=[Type]

There are parquet files in the ingestion container