cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
StmPp
Frequent Visitor

Load Data from Sql Database to CDS doesn't load Data entirely

Hi,

I would like to load Data from a Sql Server on premise Database to CDS. The reason I am doing this is, that I would like  to use the AI-Builder, which, as far as I understand, requires CDS.

 

I proceed as follows:

  • Connect to sql server
  • Power Query opens
  • Chose primary field
  • and Create new entity.

This works.

However, when checking in PowerApps there is only one or a few data sets transferred to CDS. 

Am I missing something? A refresh somewhere?

 

Thanks!

 

Power query

2020-05-05 17_07_24-Power Apps.png

Power App:

2020-05-05 16_22_46-Power Apps.png 

 

1 ACCEPTED SOLUTION

Accepted Solutions
HSheild
Super User
Super User

Hi @StmPp 

 

If you look at the Show refresh history of the dataflow then this will show if there has been any errors with the dataflow.  That might help troubleshoot.

 

 

Annotation 2020-05-06 063349.png

 

-----
 
If this post has answered your question please consider it for "Accept as Solution" or if it has been helpful give it a "Thumbs Up".

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

Someone may come along with a better answer but I've done this from MySQL. I had no luck with PowerQuery so I gave up on it. I instead created a new canvas app, added a button and then used it to kick of a ForAll to patch field for field to the CDS entity. It worked, even got binary image data to come across.

Drrickryp
Super User II
Super User II

Hi @StmPp 

I have had the best results from using the Import Data Wizard from Dynamics 365.  It can even import related data tables property into PowerApps entities.   I have found it to be the most robust and reliable method.  There is an excellent tutorial here.  https://crmbook.powerobjects.com/basics/data-management-in-microsoft-dynamics-crm/importing-data-in-... 

I have an annotated post of where to find the wizard and how to use it.  https://powerusers.microsoft.com/t5/Building-Power-Apps/Update-on-importing-relational-data-into-CDS... 

HSheild
Super User
Super User

Hi @StmPp 

 

If you look at the Show refresh history of the dataflow then this will show if there has been any errors with the dataflow.  That might help troubleshoot.

 

 

Annotation 2020-05-06 063349.png

 

-----
 
If this post has answered your question please consider it for "Accept as Solution" or if it has been helpful give it a "Thumbs Up".

View solution in original post

StmPp
Frequent Visitor

Thank you enelson906! I looked into your great idea. Unfortunately my tables have a lot of columns, so this is a lot of work. I was hoping, that I was missing something on the Power Query side and my data would load into CDS with "on board" tools and not having to program my own tool....

StmPp
Frequent Visitor

Hi @Drrickryp , 

Thank you, but I don't have Dynamics as an ERP system. So I am not able to use the wizard.

Yours

 

StmPp
Frequent Visitor

Hi @HSheild  ,

it took me a while to figure out what you where trying to tell me, as I didn't know that PA generates a dataflow for every import. It's also a bit weird having to translate program features in PowerApps from english to german.

The refresh history shows that there is a problem in my definition of datatypes.
Apparently I try to import a negative value into a number(?) field:
"Reason: Bad Request, Error code: 0x80044330, Message: A validation error occurred for cr34c_ai_op_debitor.cr34c_eur_zahlung. The value -1104142 of type System.Double is outside the valid range(0 to 1000000000)."

I assigned the CDS-datatype "decimal" to the field "EUR_ZAHLUNG":
Table.TransformColumnTypes(Navigation, {{"EUR_ZAHLUNG", type number}})

When I look into the field in the original table it gives me
EUR_ZAHLUNG
-11041.42
Why does number not support negative values?

I then tried to find out the CDS datatypes:
https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/types-of-fields
That didn't clarify things, because it says that decimal is decimal, no range indicated.

I then tried to assign other probable datatypes starting with "whole number" to the column "EUR_ZAHLUNG".
Table.TransformColumnTypes(Navigation, {{"EUR_ZAHLUNG", Int64.Type}})
Success.

Dataflow1.png

 

Dataflow2.png

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (1,464)