cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JoshSHardscape Regular Visitor
Regular Visitor

CRM Import

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!

2 REPLIES 2
Community Support Team
Community Support Team

Re: CRM Import

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.

CRM_Import

 

Best Regards,

Dale

 

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JoshSHardscape Regular Visitor
Regular Visitor

Re: CRM Import

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