Hi all!
I have a dataset that queries and API. It pulls in the data, does a few stems to convert the feed to a table and a bit of fomatting after that. I upload it and hit refesh and it refreshed fine but this is the first refresh.
After that I get this error:
| Data source error: | The '<pii>Column1</pii>' column does not exist in the rowset. |
I started to troublshoot by removing steps form the bottom up and once I removed the step Convert to Table which creates Column1 the error dissappears.
So now it reads the API data, and pulls in the list but I can't convert the list to a table to expand the data and massage it in Power Query.
Anyone else experience this? Only thing I can think of is the convert to table is assigning a different name to the column but I can't see that online.
Code:
let
Source = Json.Document(Web.Contents("https://app.cloudability.com/", [RelativePath="api/1/reporting/cost/run", Query=[
filters = "category3==12345,category3==23456,category3==34567",
dimensions = "vendor,usage_family,usage_type,account_identifier,account_name,category3,date,vendor_account_name,tag11,tag2",
metrics = "total_amortized_cost",
start_date = Date.ToText(DateTime.Date(RangeStart)),
end_date = Date.ToText(DateTime.Date(RangeEnd))
], Headers=[Authorization=APIKey]])),
results = Source[results],
Everything from here up works fine
This is the line causing the issue:
#"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
The rest of the data massaging
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"vendor", "usage_family", "usage_type", "account_identifier", "account_name", "category3", "date", "vendor_account_name", "tag11", "tag2", "total_amortized_cost"}, {"vendor", "usage_family", "usage_type", "account_identifier", "account_name", "category3", "date", "vendor_account_name", "tag11", "tag2", "total_amortized_cost"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Column1",{{"vendor", "Vendor"}, {"usage_family", "Usage Family"}, {"usage_type", "Usage Type"}, {"account_identifier", "Account Identifier"}, {"account_name", "Account Name"}, {"category3", "Cost Center"}, {"date", "Date"}, {"vendor_account_name", "Subscription Name"}, {"tag11", "Databricks Cluster"}, {"tag2", "Application"}, {"total_amortized_cost", "Total Amitorized Cost"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Subscription Name", type text}, {"Date", type datetime}, {"Cost Center", type text}, {"Account Name", type text}, {"Account Identifier", type text}, {"Databricks Cluster", type text}, {"Application", type text}, {"Usage Family", type text}, {"Vendor", type text}, {"Usage Type", type text}, {"Total Amitorized Cost", type number}})
in
#"Changed Type"
