search

microsoft / finops-toolkit

Tools and resources to help you adopt and implement FinOps capabilities that automate and extend the Microsoft Cloud.
https://aka.ms/finops/toolkit
MIT License
304 stars 105 forks source link

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

Closed jeromecalderwood closed 9 months ago

jeromecalderwood commented 11 months ago

🐛 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 commented 11 months ago

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 commented 11 months ago

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 commented 11 months ago

Thank you, I changed the query to no avail.

flanakin commented 11 months ago

@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 commented 11 months ago

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 commented 10 months ago

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 commented 10 months ago

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

thecloudman commented 10 months ago

I fixed it. Thanks

Graham Murphy 0481386343

From: Michael Flanakin @.> Sent: Friday, January 19, 2024 6:54:06 PM To: microsoft/finops-toolkit @.> Cc: Graham Murphy @.>; Comment @.> Subject: Re: [microsoft/finops-toolkit] [Power BI][Hubs] An error occurred in the ‘ftk_LoadData’ query. Expression.Error: We cannot convert the value null to type Table. (Issue #485)

CAUTION: This email originated from outside of the organisation. Do not click links or open attachments unless you recognise the sender and know the content is safe.

@jeromecalderwoodhttps://github.com/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.

— Reply to this email directly, view it on GitHubhttps://github.com/microsoft/finops-toolkit/issues/485#issuecomment-1900002681, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AG3DKGUI666W7BYDL7JGQIDYPIYC5AVCNFSM6AAAAABAQSTGZKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSMBQGAYDENRYGE. You are receiving this because you commented.Message ID: @.***> IMPORTANT INFORMATION: This email is from Australian Retirement Trust Pty Ltd (ABN 88 010 720 840, AFSL 228975) or from an Australian Retirement Trust entity: Sunsuper Financial Services Pty Ltd (ABN 50 087 154 818, AFSL 227867); Precision Administration Services Pty Ltd (ABN 47 098 977 667, AFSL 246604); QInvest Limited (ABN 35 063 511 580, AFSL 238274); QInsure Limited (ABN 79 607 345 853, AFSL 483057); ART Group Services Limited (ABN 50 125 248 286, AFSL 334546); QSuper Asset Management Pty Ltd (ABN 67 608 694 471); or ART People Services Pty Limited (ABN 90 601 938 774). Australian Retirement Trust Pty Ltd is the “Trustee” and issuer of Australian Retirement Trust (“Fund”) (ABN 60 905 115 063). Any reference to “QSuper” is reference to the Government Division of the Fund.

This email may be confidential or privileged. You are receiving this email because you provided us with your email address to send you information. You can manage your contact preferences through Member Online, by unsubscribing to any electronic marketing you receive, or by contacting us on 13 11 84 or QSuper member services on 1300 360 750. We take protecting the privacy of personal information seriously, and for more information about our privacy policy, visit Privacy Policy | Australian Retirement Trusthttps://www.australianretirementtrust.com.au/disclaimers-and-disclosures/privacy-policy

Our full email footer is available at https://www.australianretirementtrust.com.au/transmission-disclaimer

psilantropy commented 9 months ago

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 commented 9 months ago

@psilantropy does your ingestion container have data in it?

psilantropy commented 9 months ago

@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 commented 9 months ago

@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 commented 9 months ago

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 commented 9 months ago

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 commented 9 months ago

@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 commented 9 months ago

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 commented 9 months ago

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 commented 9 months ago

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 commented 9 months ago

@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 commented 9 months ago

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

arthursilvany commented 4 months ago

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