Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I'm currently trying to import data from our online CRM software, Workbooks. I have created a csv download link for a "report" with all the data I require and combined it with my api key to authorise the link. When I use this link to get data on Power BI, it recognises the data correctly and previews it as I would expect, it then automatically promotes the first row to headers and automatically changes the type of all columns. However once I apply this data, the program comes up with a "the column xxx of the table wasn't found" error. It seems odd as it is saying it can't find a column that the program automatically named based on the first row.
My advanced editor code is below if that is helpful to find a solution:
let
Source = Csv.Document(Web.Contents("https://secure.workbooks.com/data_view/989/data.csv?embed_id=default_271&_uvp_id=11179&api_key=xxxxx...",", Columns=17, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Use First Row as Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=false]),
#"Change Type" = Table.TransformColumnTypes(#"Use First Row as Headers",{{"Opportunity reference", type text}, {"Scheme name", type text}, {"Sales Manager", type text}, {"SP", Currency.Type}, {"BP", Currency.Type}, {"Stage", type text}, {"Delivery Postcode/Zipcode", type text}, {"Created Date", type text}, {"Order due date", type date}, {"Tender Date", type date}, {"Specifier Name", type text}, {"Source", type text}, {"Type", type text}, {"SP Amount", type number}, {"SP Currency", type text}, {"BP Amount", type number}, {"BP Currency", type text}})
in
#"Change Type"
Thanks in advance!
Hi @JoshSHardscape,
Did you change the blue part by hand? Its default value is "true".
#"Use First Row as Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=false]),
If it's "false", I can reproduce the same error message.
Please change it to "true" and try it again.
Best Regards,
Dale
Hi Dale,
Thanks for your reply,
The query I posted is an exact copy of what Power BI automatically generated once I chose the source, I didn't edit this in any way but I will look out for this being set to false in future and change it! On a side note I managed a temporary solution of simply removing the first fow instead of promoting it and then manually renaming columns. I'll change the Promoteallscalars variable next time I use the data and mark the answer as a solution if it works out!
Ta,
Josh