Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |