An error occurred in the ‘ftk_LoadData’ query. Expression.Error: The field 'Tags' already exists in the record

[preimerSerrala]

🐛 Problem

When trying to connect via the Cost Management connector Power BI can't load the data with the error:
An error occurred in the ‘ftk_LoadData’ query. Expression.Error: The field 'Tags' already exists in the record
This occurs on the CostSummary report.

👣 Repro steps

1. Open the CostSummary Power BI Report
2. Go to Transform data
3. Change the connector to "Cost Management connector" and enter the billing account + billing profile IDs
4. Exit transform data with save and apply

🤔 Expected

The data is getting updated from the Azure Cost Management API

Thank you and best regards,

Pascal

[ghost]

I have the same problem.

An error occurred in the ‘ftk_LoadData’ query. Expression.Error: The field 'Tags' already exists in the record.
Details:
Name=Tags
Value=

[flanakin]

We separated the connector from hubs due to issues it was causing in the Power BI service. This should be working in the new CostManagementConnector report.

@p-reimer, can you try this new report: https://github.com/microsoft/finops-toolkit/raw/dev/src/power-bi/CostManagementConnector.pbix

@baptistepa, are you also using the Cost Management connector? If so, can you also try that?

I'll move this issue to 0.2.1 to keep track of it, but please do let me know if this resolves the issue.

[ghost]

I am getting an error when opening the file...

[github-actions]

Uh oh! @baptistepa, the image you shared is missing helpful alt text. Check #493 (comment).

Alt text is an invisible description that helps screen readers describe images to blind or low-vision users. If you are using markdown to display images, add your alt text inside the brackets of the markdown image.

Learn more about alt text at Basic writing and formatting syntax: images on GitHub Docs.

[preimerSerrala]

Dear Michael,

thank you for the update. Unfortunately with the new report the same issue persists with some additional errors when connecting to a MCA account using the manual input scope.

[github-actions]

Uh oh! @p-reimer, the image you shared is missing helpful alt text. Check #493 (comment).

Alt text is an invisible description that helps screen readers describe images to blind or low-vision users. If you are using markdown to display images, add your alt text inside the brackets of the markdown image.

Learn more about alt text at Basic writing and formatting syntax: images on GitHub Docs.

[flanakin]

@p-reimer Can you email me at micflan@ms? (Spell out the company name + ".com". Hiding from bots.)

[flanakin]

I am getting an error when opening the file...

@baptistepa, can you confirm which file this was? That setting gets reset every time we open the reports, so it's constantly causing issues and we don't have a way to prevent it or automate the value.

[flanakin]

Update: Scheduled a meeting with @preimerSerrala to investigate further.

[flanakin]

We noticed the connector for MCA has both a tags and a Tags column, which is what's causing the issue. As a quick fix, anyone who is running into this can replace lines 3 and 4 in the CostDetails query with:

    AmortizedDataset_internal = Connector{[Key="usagedetailsamortized"]}[Data],
    ActualDataset_internal = Connector{[Key="usagedetails"]}[Data],

    // TODO: Confirm if Tags ever has data. If it does, merge both tags and Tags columns into a single column.
    AmortizedDataset = if Table.HasColumns(AmortizedDataset_internal, "tags") and Table.HasColumns(AmortizedDataset_internal, "Tags") then Table.RenameColumns(AmortizedDataset_internal, {"Tags", "Tags2"}) else AmortizedDataset_internal,
    ActualDataset    = if Table.HasColumns(ActualDataset_internal,    "tags") and Table.HasColumns(ActualDataset_internal,    "Tags") then Table.RenameColumns(ActualDataset_internal,    {"Tags", "Tags2"}) else ActualDataset_internal,

Please note this is a quick fix. I am not sure why there are 2 columns for tags. Ideally, we should be merging both into a single column. This renames one so you will see Tags and __NEW_Tags2. The "_NEW" prefix is something we have to help us catch unexpected columns. We'll make sure that's address in the 0.2.1 update. This is just a quick workaround. Apologies for the inconvenience 😥

[flanakin]

We also noticed an issue with the recommendation queries for MCA where some columns were missing. You can replace those with the following:

let
    Source = ftk_Connector("rirecommendationsshared"),
    NormalizedSize = if Table.HasColumns(Source, "normalizedSize") then Source else Table.AddColumn(Source, "normalizedSize", each null),
    RecommendedQuantityNormalized = if Table.HasColumns(NormalizedSize, "recommendedQuantityNormalized") then NormalizedSize else Table.AddColumn(NormalizedSize, "recommendedQuantityNormalized", each null, Int64.Type),
    SelectColumns = Table.SelectColumns(RecommendedQuantityNormalized, {"meterId", "term", "costWithNoReservedInstances", "recommendedQuantity", "totalCostWithReservedInstances", "netSavings", "firstUsageDate", "scope", "lookBackPeriod", "instanceFlexibilityRatio", "instanceFlexibilityGroup", "normalizedSize", "recommendedQuantityNormalized", "skuProperties"}),
    ChangeType = Table.TransformColumnTypes(SelectColumns, {{"instanceFlexibilityRatio", Int64.Type}, {"recommendedQuantityNormalized", Int64.Type}})
in
    ChangeType

Please note the Source line is different between each of these:

• For RecommendationsSingle, use: Source = ftk_Connector("rirecommendationssingle"),
• For RecommendationsShared, use: Source = ftk_Connector("rirecommendationsshared"),

[flanakin]

For anyone who needs this before the 0.2.1 release, you can download the latest from the dev branch.