Common Data Service Dataflows for Import from SharePoint and bulk Edit with Excel

[@zaidkashour]

CDS is way better than Share Point online lists. I use more than 10 entities connected to each other with lookup fields. In Power Apps canvas apps, I stopped using filters in each gallery, I just change the entity source and data loads very quickly. The hard thing to do is making complex operations in Model Driven apps the same way we do in canvas apps! Thanks Shane for bringing more CDS topics to the channel :)

[@ShanesCows]

You are welcome Zaid. Looking forward to doing more soon. 🤓

[@paulhaley5876]

@Zaid - Have you had any problems creating a lookup between two custom entities? When trying to load the data with Power Query, we keeping getting an error saying the lookup field in the parent entity cannot be found. If the parent is a Standard entity, such as Contact, the lookup works fine.

[@zaidkashour]

@@paulhaley5876 Hi Paul, I am not a developer so my method may not be suitable for you. I actually export my SP list to Excel and then upload it to a custom CDS, there is a problem when I export data from SP to CDS. I upload the list only once then I modify certain records either from a Model Driven app or through Edit in Excel option. All the relationships between my entities are referential.

[@br31woodlands69]

Your intro rocks. I never get bored with it. Honestly watch the whole video because of your intro.

[@ShanesCows]

Thanks 😌

[@jimfitch]

What great tools! Thanks for the clear explanation & usage tips!

[@ShanesCows]

You're very welcome!

[@benjaminmillar5584]

Great videos Shane! Just caught up on a load of them in one go really easy to understand and honest. Great work.

[@ShanesCows]

Glad you enjoyed them Benjamin

[@ewolthaus]

Great work again, Shane. Love your work and effort to create these videos. Thank you!

[@ShanesCows]

My pleasure!

[@brt007p]

You are the man Shane!! thanks for sharing !!

[@ShanesCows]

Glad to help. 😀

[@paulhaley5876]

Those two tips at the end - using the GUID in Edit in Excel and don't touch the keyboard when refreshing Power Query - are worth their weight in gold. If I had known those weeks ago I could have saved hours of frustration - you have probably saved my marriage..... I would encourage people to use Solutions if they are creating Entities, Apps and Flows otherwise it can all get very messy. I still have some problems where lookup fields can't be found by the child entity; and Option Sets will sometimes take a text value but other times ask for an integer.

[@ShanesCows]

Glad it helped Paul.

[@adamwheeler1702]

You are definitely smart enough to teach Power Query especially as it relates to Data Flows. I am really struggling with how to take a Person column in SP and move that to User DataVerse lookup column via DataFlow. Why do they make this so difficult?????

[@stoffer182]

Love the videos Shane! One question. At the end of the video you mention that on several occations the refresh caused duplicates of all your values. This happens for me and I can't for the life of me figure out why and how to deal with it. Have you figured it out yet? Best regards!

[@ShanesCows]

I haven't done enough with Dataflows since to understand it either. Sorry

[@midds44]

@@ShanesCows I'm pulling data from a SQL Server connection into Dataflows with a daily refresh and getting duplicates every day (I expected an overwrite). Have you worked more with Dataflows since this comment and been able to understand the refresh function any better?

[@myboyzdad]

I have a SharePoint List that I’d like to move to CDS. With this videos, I should be able to accomplish this task. Thank you. The big question: I have a PowerrApp that is connected to the SharePoint list, once I have the list in CDS, will the connection from my PowerApp to that source be seemless? Just connect to the new Entity, and change the source in PoweraApp? Easy peezy?

[@ShanesCows]

Probably not. You will most likely have to touch a a bunch of little things. Good news error checking will catch them all so you can just fix what it tells you to after switches datasource

[@studiovolantis6650]

Wish this was available in the Teams Power Apps environment .

[@ShanesCows]

Agree. I do have a couple of things you can try until that day: Import from SharePoint kzbin.info/www/bejne/o6e6faCmoLhlkNk Use Excel to manipulate Dataverse kzbin.info/www/bejne/mqe7mZ2nrNKaaas

[@earllinealcide3365]

Hi Shane, was there a reason for you choosing 'Remove Columns' and not using 'Choose Columns' when you were in Power Query?

[@ShanesCows]

Nope. Either should be fine I think

[@JGNiDK]

At 8:30 your're setting a primary name field. Why title? Couldn't you have two with the same title? Or isn't a unique ID field? Or should I just watch the last video?

[@ShanesCows]

It isn’t a unique ID that is a field CDS maintains. This just the field that shows for lookups and such.

[@ksowjanya4488]

This video is great and helpful. Thank you for this video. I have a doubt.I am importing a csv file from sharepoint to dataverse using dataflow in dataverse. After I select Binary of required table, my flow stops at navigation step. I could not see the table.can you please tell what might be the reason?

[@pkp044]

Very helpful video Shane!! Thank you so much!! While exporting data from SharePoint to Dataverse I found couple of columns called Attachment and Attachments! How are they different?

[@ShanesCows]

They have files in them. I haven’t ever tried to import or move them. Not sure you can

[@pkp044]

@@ShanesCows Thank you Shane!! Is there any guidelines we need to follow while migrating look up column from SharePoint

[@manasdas11155]

Hey Shane thanks for the video , but i have a query i followed the same steps to get the data from SP List but when i tried it only got the Primary Column data rest other columns were not brought to Dataverse nor the columns or the Data's present in them

[@uploadsnovatrack5856]

Hi Shane, this is totally unrelated with the current video, but I wanted to ask if you could do a tutorial on the Google sheet flow connector. I tried to use it with the Twitter connector to insert a rows in the google sheet file I created everytime I get mentioned in I tweet, but it just wasn't working.

[@ShanesCows]

I have never used any of the Google pieces. 😔

[@2007pradipta]

Good one ...if i delete or add or modify in powerapps does it update SharePoint? Or only update CDS

[@ShanesCows]

It will only update the data source you change. It doesn't keep the two in sync.

[@ashokpershad]

Thanks a lot Shane...liked your video though CDS is something not comes free to account I use. It always ask for some special access level.

[@ShanesCows]

Yes CDS is a premium connector.

[@VerdonTrigance]

I was playing with power query previously, but don't remember is it possible to skip rows if some data corrupted? For example if you are importing data from excel to sharepoint list.

[@ShanesCows]

I am not sure. 🤔

[@philipegomes-powerlearn555]

HI Shane Excellent video!! I learn a lot about power apps following your series. I have a question about the amount of data that could go into CDS in a plan 1 licence. I saw in the Microsoft site that says 50MB and I think that couldn't be right. Could you help to better understand that storage management question? Thank you in advance :)

[@ShanesCows]

Long story short. YOu get 1 GB of storage plus 50 MB per licensed user. You can also buy additional storage in 1 GB increments. So you should be good.

[@slopeztello]

Excellent! Have you tried connecting to an API or a website? I´m trying to create an app that includes countries and cities as well as addresses and I was looking to have address completion for US addresses and other countries if possible.

[@ShanesCows]

I usually use power Automate when I need to talk to an api. 😀

[@mammifero]

Great video! one question: in a real world scenario you mantain both Sharepoint List and CDS Entities aligned or you choose only one enviroment to develop your application ?

[@ShanesCows]

Only one. But some people want to move data from SharePoint to Dataverse. That was my main goal of the video.

[@AssassinUK]

Excellent .. needed a video about the excel add-in as I never really got this to work. Plus 1st comment smugness :-)

[@ShanesCows]

Yeah that add-in is amazing!

[@nebojsabudzogan2109]

Please which license/subcription do I need to be able to do this?

[@ShanesCows]

I don't think you need special license to import or work with the data. Licnesing should only come up when you build an app using hte Dataverse (CDS) data.

[@365degree9]

Hi Shane, thanks for the great video. Regarding the data update, if we get data updated in SharePoint, we can have it refreshed in CDS, but not the reversed way (ie, data updated in CDS will not be reflected or updated in Sharepoint). Is my understanding correct? thanks

[@ShanesCows]

I think so.

[@ewolthaus]

@@ShanesCows Yes, data sync is only one way: SharePoint > CDS

[@vinitchauhan6265]

May i know about any way to get data updated from CDS entity dataflow to SharePoint..vice versa

[@ShanesCows]

Dataflows for importing to CDS. You could write a flow to pull from CDS and push to SharePoint.

[@vinitchauhan6265]

@@ShanesCows thanks for the reply Want to know how to show powerapps form based on CDS in SharePoint page

[@ewolthaus]

Please note: as far as I know, having the data sync from SharePoint (or any other source) to the CDS, it will override your changes to the data. Good to know if you plan to sync on a regular basis but also will edit the data in the CDS.

[@ShanesCows]

Interesting. I will have to play with it more.

[@richardclowes6166]

"Tells time like my wife" - man, you live dangerously!

[@ShanesCows]

Strong faith she doesn’t watch. 😂

[@Fernando_Calero]

I'm getting an error while finishing creating the new table: "We encountered an unexpected error. Mashup text". Does anyone have the same issue?

[@ShanesCows]

I am not sure why. Sorry.

[@vitorferreira384]

Great video Shane. Just one thing, if I delete something in sharepoint List, and refresh to CDS, this item is going to be deleted/modified in CDS?

[@ShanesCows]

I don’t think so but maybe I have not tested

[@ewolthaus]

@@ShanesCows Depends on the way you configure the Power Query. You can check a checkbox to enable deletion. When you do, you also have to have an ID so the dataflow can use this to compare and delete.

[@PLBNJ]

Does moving to and using data in CDS eliminate the delegation issues inherent with SharePoint? BTW... your videos have been most helpful in learning to use Power Apps, thank you.

[@ShanesCows]

Hey Patrick. CDS does a better job but still can have delegation issues

[@JarrodMast]

Great video thanks Shane, perfect timing! But I do think there are signs you may be spending too much time talking to yourself in front of a camera 😁

[@ShanesCows]

Yes. Next question for Shane. 😂

[@shubhamchavan7338]

how to get data from excel in common data service?

[@ShanesCows]

Use the data flow and chose Excel as the datasource

[@nomialijee]

Nice

[@ShanesCows]

Thanks 😊

[@abdulkhadir2661]

Hi Shane ... I have excel which is not in table format which I want to import in Powerapps .. I haven't kept in table format because excel is in shared mode .. if it's in shared mode table format is not possible .. please help

[@ShanesCows]

Not sure if I have an answer. Has to be a table.

[@abdulkhadir2661]

@@ShanesCows ok thanks for prompt response . What I have done other turn around is .. save that table to SQL using VB script and from SQL I have connected to Powerapps