Error: we cannot convert the value null to type Table.

[SamadSaqlain]

🐛 Problem

Getting and error stating its null when there is not empty data to be considered as null

👣 Repro steps

after following the steps of the cost summary

🤔 Expected

throw an error in the final steps

[MS-mikeo]

I was able to spin up this up in v0.1.1 (was having trigger issues in the latest version). A single parquet file is showing in my ingestion folder, but I receive the "Error: we cannot convert the value null to type Table" in PowerBI when trying to apply my datalake storage account URL.

[flanakin]

The issue here is that 0.2 requires FOCUS exports and 0.1.* uses amortized exports. The Power BI reports will ignore the other exports since they do not apply. This results in no data, which is when you see "null". Please make sure to use the Power BI reports for the version you have deployed.

We did identify a bug in 0.2 and have issued a release candidate here: https://aka.ms/finops/hubs/rc.

@SamadSaqlain / @MS-mikeo, can y'all both try the release candidate with 0.2 Power BI reports and let me know if you're still seeing this?

I'll use this issue to track adding error handling. I'm not sure how to best do that but I have some ideas. I'm currently working on a new Data ingestion report but I'd like to find a way to add this into the Power BI queries themselves since it doesn't make sense to use a separate report to debug the first one 😜 Would love any thoughts on these approaches.

[erincon01]

@flanakin, I deployed the v.0.2 you shared on friday and ADF moves the files from msexports to ingestion. Trigger is enabled too.

However, this RC, fails: "Deployment template validation failed: 'The template resource '/add' for type 'Microsoft.KeyVault/vaults/accessPolicies' at line '1' and column '3482' has incorrect segment lengths. A nested resource type must have identical number of segments as its resource name. A root resource type must have segment length one greater than its resource name. Please see https://aka.ms/arm-syntax-resources for usage details.'. (Code: InvalidTemplate)

[erincon01]

@flanakin .

the issues with the date column is related to the expected folders.

can you confirm you are expecting this structure from the Focus Exports?

• msexports: it set when you deploy the hub.
• subscription_id: set when you deploy the hub.
• costes_diarios: name of the export.
• date ranges: automatically set from the Export.
• GUID value: automatically created from the Export. New GUID in every export.

I believe ADF duplicates data every time the trigger got activated. The ADF processs tries to remove the preview file, but as the GUID folder is new, nothing is removed.

According to the Power Query transformation, I believe you are expecting this:

https://storage_container.dfs.core.windows.net/ingestion/subscription_id/costes-diarios/20240101-20240131/202402/focuscost/datafile.parquet

because in the ftk_Hub function you run this: StartDate = Table.AddColumn(Month, "StartDate", each [Month] & "01"),

in my case, I have this structure:

https://storage_container.dfs.core.windows.net/ingestion/subscription_id/costes-diarios/20240101-20240131/guid_from_exports/focuscost/datafile.parquet

Instead of using this code:

// Parse folder path
Dataset   = Table.AddColumn(FilterHiddenFiles, "Dataset",   each List.Last(List.RemoveLastN(Text.Split([Folder Path],"/"), 1))),
Month     = Table.AddColumn(Dataset,           "Month",     each List.Last(List.RemoveLastN(List.RemoveLastN(Text.Split([Folder Path],"/"), 1), 1) )),
// TODO: Update date handling for raw exports
StartDate = Table.AddColumn(Month,             "StartDate", each [Month] & "01"),
StartDate_type = Table.TransformColumnTypes(StartDate, {{"StartDate", type date}}),
EndDate   = Table.AddColumn(StartDate_type,    "EndDate",   each Date.EndOfMonth([StartDate])),

// Only FOCUS costs
// TODO: Get metric from the manifest
FilterFilesByMetricAndFileType = Table.SelectRows(EndDate, each [Dataset] = dataset and [Extension] = ext),

I've changed my code to this:

// Parse folder path
Dataset   = Table.AddColumn(FilterHiddenFiles, "Dataset",   each List.Last(List.RemoveLastN(Text.Split([Folder Path],"/"), 1))),
Month     = Table.AddColumn(Dataset,           "DatesFromTo",     each List.Last(List.RemoveLastN(List.RemoveLastN(Text.Split([Folder Path],"/"), 2), 1) )),
#"Split Column by Delimiter" = Table.SplitColumn(Month, "DatesFromTo", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"StartDate", "EndDate"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"StartDate", type date}, {"EndDate", type date}}),
// Only FOCUS costs
// TODO: Get metric from the manifest
FilterFilesByMetricAndFileType = Table.SelectRows(#"Changed Type", each [Dataset] = dataset and [Extension] = ext),

The Power BI loads the data, but the duplicated data issue is there. It is important to aggree in the data folders issue. It may happend that you are using another export process.

This is my export manifest.json:

  "exportConfig": {
    "exportName": "costes-diarios",
    "resourceId": "/subscription_id/my_subscription/providers/Microsoft.CostManagement/exports/costes-diarios",
    "dataVersion": "1.0-preview(v1)",
    "apiVersion": "2023-07-01-preview",
    "type": "FocusCost",
    "timeFrame": "MonthToDate",
    "granularity": "Daily"
  },
  "deliveryConfig": {
    "partitionData": true,
    "dataOverwriteBehavior": "OverwritePreviousReport",
    "fileFormat": "Csv",
    "containerUri": "/subscriptions/my_subscription/resourceGroups/Default-Storage-NorthEurope/providers/Microsoft.Storage/storageAccounts/my_storage_account",
    "rootFolderPath": "my_subscription"
  },
  "runInfo": {
    "executionType": "OnDemand",
    "submittedTime": "2024-02-04T11:56:47.6814605Z",
    "runId": "guid",
    "startDate": "2024-02-01T00:00:00",
    "endDate": "2024-02-04T00:00:00Z"
  },
  "blobs": [
    {
      "blobName": "my_subscription_id/costes-diarios/20240201-20240229/guid_from_exports/part_0_0001.csv",
      "byteCount": 1371536
    }
  ]
}

As suggestion, it would be nice to share in the documentation the folders structure in the data processing worflow: https://microsoft.github.io/finops-toolkit/hubs/data

[flanakin]

However, this RC, fails: "Deployment template validation failed: 'The template resource '/add'..."

I believe that was an old template. I updated it so it should be good now. Sorry for the inconvenience. I was able to validate a successful deployment with it.

https://aka.ms/finops/hubs/rc

can you confirm you are expecting this structure from the Focus Exports?

• msexports: it set when you deploy the hub.
• subscription_id: set when you deploy the hub.
• costes_diarios: name of the export.
• date ranges: automatically set from the Export.
• GUID value: automatically created from the Export. New GUID in every export.

Cost Management controls the folder hierarchy in msexports. We do expect it to be in a particular structure so we can identify the scope. They changed the hierarchy, so we had to get a little creative. This was the source of one of the bugs we hit in 0.2.

The only thing I'd call out from what you mentioned is that we ask that exports be configured to set the path to the full scope ID. For a subscription, this would be "subscriptions/###". The reason for that is to avoid a long list of GUIDs from different types of scopes. At least this will group them a bit. That said, this isn't required. We don't explicitly require the scope ID. It's just a suggestion today. That will likely change in the future as we pull more data and will need to map it back to a location in storage.

I believe ADF duplicates data every time the trigger got activated. The ADF process tries to remove the preview file, but as the GUID folder is new, nothing is removed.

Each file gets processed when created today. If a file is left behind, then that file didn't get ingested. We need to add some error handling to help raise awareness of ingestion failures. We have longer-term plans for that, but just aren't there yet. When data is moved to ingestion, it replaces the old files so you should never have duplicated data. If you do, then that's a bug.

According to the Power Query transformation, I believe you are expecting this: https://storage_container.dfs.core.windows.net/ingestion/subscription_id/costes-diarios/20240101-20240131/202402/focuscost/datafile.parquet

We expect ingestion/subscriptions/###/202401/focuscost/###.parquet. I'll add that to the docs. That's a good idea!

Note there was a bug in the first release candidate I shared that didn't set the path correctly. That was fixed in rc2.

[erincon01]

I believe that was an old template. I updated it so it should be good now. Sorry for the inconvenience. I was able to validate a successful deployment with it.

https://aka.ms/finops/hubs/rc

yes. I've just deployed this RC. It successed!

[erincon01]

Cost Management controls the folder hierarchy in msexports. We do expect it to be in a particular structure so we can identify the scope. They changed the hierarchy, so we had to get a little creative. This was the source of one of the bugs we hit in 0.2.

The only thing I'd call out from what you mentioned is that we ask that exports be configured to set the path to the full scope ID. For a subscription, this would be "subscriptions/###". The reason for that is to avoid a long list of GUIDs from different types of scopes. At least this will group them a bit. That said, this isn't required. We don't explicitly require the scope ID. It's just a suggestion today. That will likely change in the future as we pull more data and will need to map it back to a location in storage.

I believe ADF duplicates data every time the trigger got activated. The ADF process tries to remove the preview file, but as the GUID folder is new, nothing is removed.

Each file gets processed when created today. If a file is left behind, then that file didn't get ingested. We need to add some error handling to help raise awareness of ingestion failures. We have longer-term plans for that, but just aren't there yet. When data is moved to ingestion, it replaces the old files so you should never have duplicated data. If you do, then that's a bug.

According to the Power Query transformation, I believe you are expecting this: https://storage_container.dfs.core.windows.net/ingestion/subscription_id/costes-diarios/20240101-20240131/202402/focuscost/datafile.parquet

We expect ingestion/subscriptions/###/202401/focuscost/###.parquet. I'll add that to the docs. That's a good idea!

@flanakin , I confirm that data is transformed good to the ingestion folder.

I'd close the issue as fixed.

[erincon01]

@flanakin. No changes needed in the Power Query transformation. Date filters work fine! All is good!

[MS-mikeo]

The issue here is that 0.2 requires FOCUS exports and 0.1.* uses amortized exports. The Power BI reports will ignore the other exports since they do not apply. This results in no data, which is when you see "null". Please make sure to use the Power BI reports for the version you have deployed.

We did identify a bug in 0.2 and have issued a release candidate here: https://aka.ms/finops/hubs/rc.

@SamadSaqlain / @MS-mikeo, can y'all both try the release candidate with 0.2 Power BI reports and let me know if you're still seeing this?

I'll use this issue to track adding error handling. I'm not sure how to best do that but I have some ideas. I'm currently working on a new Data ingestion report but I'd like to find a way to add this into the Power BI queries themselves since it doesn't make sense to use a separate report to debug the first one 😜 Would love any thoughts on these approaches.

I deployed the rc version above and setup a new export so that everything was clean. I checked and the export and it said it had ran, but no files in msexports. So I deleted it and tried to setup again (making sure I used the link for v2 exports) and now I can no longer create FOCUS schema exports. I am wondering if something changed and that is why my export earlier wasn't working properly. Is this working for others today?

[flanakin]

@erincon01 Here's a PR for the folder hierarchy docs. Mind reviewing that and leaving any comments or signing off if that feels good?

[flanakin]

I can no longer create FOCUS schema exports.

@MS-mikeo I'm seeing the same. Let me follow up with the team to see if something changed. I believe the feature is also available via the Try preview command at the top of the Overview or Cost analysis pages, so you could try enabling "Exports preview" at the bottom of that list in the meantime.

[MS-mikeo]

I can no longer create FOCUS schema exports.

@MS-mikeo I'm seeing the same. Let me follow up with the team to see if something changed. I believe the feature is also available via the Try preview command at the top of the Overview or Cost analysis pages, so you could try enabling "Exports preview" at the bottom of that list in the meantime.

That worked by flipping the Try Preview switch, thanks! However, I noticed ingestion did not end up with files after & I had no trigger in ADF when I checked. I am going to try to deploy the RC one more time.

[MS-mikeo]

@flanakin I am configured and working now using the RC and v0.2 PowerBI report. Exports are working for multiple scopes as expected. Thanks!

[SamadSaqlain]

Used the v2 version but still was not able to resolve the issue.

[MS-mikeo]

Did you redeploy the RC version of the Finops Hub @SamadSaqlain?

[flanakin]

@SamadSaqlain were you able to get this working?

https://aka.ms/finops/hubs/rc

[sdaubaras726]

@flanakin I am having this issue as well. I have deployed FinOps hubs 0.2.1-rc3 and am using the v2 version of the PowerBi file. I see the same "We cannot convert the value null to type Table" error user @SamadSaqlain did.

[flanakin]

@sdaubaras726 Can you try with the 0.3 release we just published? If you're still hitting it, let me know which report you're seeing the issue with. Also try the new Data ingestion report which will give you some insight into what data has been exported and ingested. I'll move this to 0.4 as we wait to confirm.

[sdaubaras726]

@flanakin Is there a Deploy button that is working for v0.3? The issue I was seeing was with the CostSummary report. I pulled down the Data Ingestion report and am seeing the following.

[flanakin]

@sdaubaras726 Were you able to confirm this? The latest can be downloaded from https://aka.ms/finops/hubs.

[flanakin]

@sdaubaras726, I'm going to close this since we haven't heard back in a couple months. If you're still facing an issue, please create a new issue and we'll track it down for you.