{"id":1100,"date":"2024-09-11T18:02:00","date_gmt":"2024-09-11T23:02:00","guid":{"rendered":"https:\/\/automatethemundane.com\/index.php\/2024\/09\/11\/dataflows-walkthrough\/"},"modified":"2024-09-11T18:02:00","modified_gmt":"2024-09-11T23:02:00","slug":"dataflows-walkthrough","status":"publish","type":"post","link":"https:\/\/automatethemundane.com\/index.php\/2024\/09\/11\/dataflows-walkthrough\/","title":{"rendered":"Dataflows walkthrough"},"content":{"rendered":"\n

Dataflows are a powerful tool for importing large amounts of data from numerous data sources into Dataverse. Being such a powerful tool, certain steps need to be followed to ensure things like lookups and datatypes are handled correctly. The process I use is a very conservative approach as I like to ensure data is correct throughout the process. This guide will be broken down into three parts.<\/p>\n\n\n

Creating Dataverse table from Single CSV<\/h1>\n\n\n

Creating a Dataverse table from a csv is a method that can be used, though not one I would use on a frequent basis. I would have the csv file saved in a SharePoint Library to showcase how to manage a file that is stored there. <\/p>\n\n\n

The CSV I would have 20 rows with some random strings and int in it. <\/p>\n\n\n

String<\/th>Int<\/th><\/tr><\/thead>
CHAR-UQO<\/td>866<\/td><\/tr>
CHAR-GAA<\/td>370<\/td><\/tr>
CHAR-HRE<\/td>498<\/td><\/tr>
CHAR-EKF<\/td>432<\/td><\/tr>
CHAR-VFZ<\/td>386<\/td><\/tr>
CHAR-EIF<\/td>612<\/td><\/tr>
CHAR-ZZS<\/td>189<\/td><\/tr>
CHAR-USD<\/td>486<\/td><\/tr>
CHAR-CDZ<\/td>335<\/td><\/tr>
CHAR-TVZ<\/td>525<\/td><\/tr>
CHAR-RRT<\/td>595<\/td><\/tr>
CHAR-MVT<\/td>364<\/td><\/tr>
CHAR-OLY<\/td>717<\/td><\/tr>
CHAR-UKM<\/td>220<\/td><\/tr>
CHAR-UKP<\/td>962<\/td><\/tr>
CHAR-NEQ<\/td>535<\/td><\/tr>
CHAR-IZA<\/td>488<\/td><\/tr>
CHAR-BTU<\/td>582<\/td><\/tr>
CHAR-DUT<\/td>638<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n

Step one would be to go into the maker portal and create a New Datflow called New Table from CSV<\/p>\n\n\n

\"\"\/<\/figure>\n\n\n

Then select SharePoint folder<\/p>\n\n\n

\"\"\/<\/figure>\n\n\n

Enter in the URL for the site and Authentication Type to Organizational account and then Sign in<\/p>\n\n\n

\"\"\/<\/figure>\n\n\n

Once the connection is made, press Next.<\/p>\n\n\n

A list of all the items in the SP library selected will be displayed. The one I want is called Examplecsv1.csv. Select Transform to bring in all the data. \n<\/p>\n\n\n

\"\"\/<\/figure>\n\n\n

Once in Power Query Select Binary from the csv you want to load. <\/p>\n\n\n

\"\"\/<\/figure>\n\n\n

Power Query will then do its best to transform the data into the correct types. Lucky for me Power Query got it all correct. But if it did not, I could massage it as needed. All I have to do is select Next. <\/p>\n\n\n

\"\"\/<\/figure>\n\n\n

Now I can select Load to new table, set the table name, and build out the columns. With all of it set, hit Next and Publish. \n<\/p>\n\n\n

\"\"\/<\/figure>\n\n\n

My data is now loaded as expected.<\/p>\n\n\n

\"\"\/<\/figure>\n\n\n

Loading from single CSV to Single Table<\/h1>\n\n\n

The single csv will load to a table called My Table 1 it will have 2 columns, String Colum, and Int Column<\/p>\n\n\n

erDiagram\n    My_Table\n    My_Table{\n        string String_Column PK\n        int Int_Column\n    }\n    <\/code><\/pre>\n\n\n
\n
\n
\"\"\/<\/figure>\n\n<\/div>\n\n\n
\n
\"\"\/<\/figure>\n\n<\/div>\n\n<\/div>\n\n\n
In the Power Apps Maker studio, I have created a new Dataflow called \u201cSingle CSV to Single Table\u201d<\/p>\n\n\n
\"\"\/<\/figure>\n\n\n
To make this example simple, I am just going to manually upload the csv into the dataflow<\/p>\n\n\n
\"\"\/<\/figure>\n\n\n
Power Query will now launch giving me a view of the data\n<\/p>\n\n\n
\"\"\/<\/figure>\n\n\n
As an exercise, I will click on Transform Table, but because it all loaded correctly I really do not need to. <\/p>\n\n\n
I again see that the data has been brought in correctly and does not require transformations<\/p>\n\n\n
\"\"\/<\/figure>\n\n\n
I have mapped the columns from the csv to the Dataverse table<\/p>\n\n\n
\"\"\/<\/figure>\n\n\n
All I need to do now is select Next and Publish<\/p>\n\n\n
Loading data into multiple related Dataverse from multiple related csv files<\/h1>\n\n\n
The csv data is as follows\n<\/p>\n\n\n
\n
\n
Parent table<\/strong><\/p>\n\n\n
String<\/td>Int<\/td><\/tr>
STRING-1<\/td>642<\/td><\/tr>
STRING-2<\/td>893<\/td><\/tr>
STRING-3<\/td>600<\/td><\/tr>
STRING-4<\/td>515<\/td><\/tr>
STRING-5<\/td>774<\/td><\/tr>
STRING-6<\/td>442<\/td><\/tr>
STRING-7<\/td>546<\/td><\/tr>
STRING-8<\/td>619<\/td><\/tr>
STRING-9<\/td>522<\/td><\/tr>
STRING-10<\/td>256<\/td><\/tr>
STRING-11<\/td>662<\/td><\/tr>
STRING-12<\/td>384<\/td><\/tr>
STRING-13<\/td>952<\/td><\/tr>
STRING-14<\/td>736<\/td><\/tr>
STRING-15<\/td>244<\/td><\/tr>
STRING-16<\/td>146<\/td><\/tr>
STRING-17<\/td>942<\/td><\/tr>
STRING-18<\/td>462<\/td><\/tr>
STRING-19<\/td>956<\/td><\/tr>
STRING-20<\/td>381<\/td><\/tr><\/tbody><\/table><\/figure>\n\n<\/div>\n\n\n
\n
Child table<\/strong><\/p>\n\n\n
String<\/td>Int<\/td>Lookup<\/td><\/tr>
SECOND-1<\/td>560<\/td>STRING-1<\/td><\/tr>
SECOND-2<\/td>134<\/td>STRING-13<\/td><\/tr>
SECOND-3<\/td>448<\/td>STRING-17<\/td><\/tr>
SECOND-4<\/td>770<\/td>STRING-15<\/td><\/tr>
SECOND-5<\/td>833<\/td>STRING-6<\/td><\/tr>
SECOND-6<\/td>894<\/td>STRING-6<\/td><\/tr>
SECOND-7<\/td>294<\/td>STRING-2<\/td><\/tr>
SECOND-8<\/td>563<\/td>STRING-8<\/td><\/tr>
SECOND-9<\/td>889<\/td>STRING-9<\/td><\/tr>
SECOND-10<\/td>299<\/td>STRING-2<\/td><\/tr>
SECOND-11<\/td>582<\/td>STRING-11<\/td><\/tr>
SECOND-12<\/td>484<\/td>STRING-3<\/td><\/tr>
SECOND-13<\/td>373<\/td>STRING-19<\/td><\/tr>
SECOND-14<\/td>442<\/td>STRING-19<\/td><\/tr>
SECOND-15<\/td>951<\/td>STRING-4<\/td><\/tr>
SECOND-16<\/td>731<\/td>STRING-10<\/td><\/tr>
SECOND-17<\/td>391<\/td>STRING-16<\/td><\/tr>
SECOND-18<\/td>658<\/td>STRING-18<\/td><\/tr>
SECOND-19<\/td>336<\/td>STRING-9<\/td><\/tr>
SECOND-20<\/td>713<\/td>STRING-16<\/td><\/tr><\/tbody><\/table><\/figure>\n\n<\/div>\n\n<\/div>\n\n\n
The first step is to create the two Dataverse tables. They will be called Parent and Child tables. In the Child table, I have created a lookup to the child table called \u201cParent Lookup\u201d<\/p>\n\n\n
\"\"\/<\/figure>\n\n\n
In the Parent table, I have created a key called Parent using the primary name. This will ensure that the data load works for the lookup column<\/p>\n\n\n
\"\"\/<\/figure>\n\n\n
Then, I have loaded the data into the Parent table using the same process as above. Once all that data is loaded, I will create a new dataflow called Child Data flow and upload the csv<\/p>\n\n\n
\"\"\/<\/figure>\n\n\n
With the data loaded, I will then import in the parent table from Dataverse. Select Get data, then Dataverse. Use the login controls to make a connection to Dataverse and bring in the Parent table<\/p>\n\n\n
\"\"\/<\/figure>\n\n\n
With both datasets loaded, I need to update some security settings, select Options, Privacy and allow combining of data.<\/p>\n\n\n
\"\"\/<\/figure>\n\n\n
\"\"\/<\/figure>\n\n\n
Now select Merge queries as new to join the two tables<\/p>\n\n\n
\"\"\/<\/figure>\n\n\n
Match the child table with the parent table based on the primary name<\/p>\n\n\n
\"\"\/<\/figure>\n\n\n
Select the expand columns then select the primary name of the parent table (name for this example).<\/p>\n\n\n
\"\"\/<\/figure>\n\n\n
With the merge created, set the other two queries to do not load. Then Select Next<\/p>\n\n\n
\"\"\/<\/figure>\n\n\n
Map the columns to the appropriate one, ensuring that the expanded column above goes to the primary name field of the parent column. Select Publish and watch the data be loaded. <\/p>\n\n\n
\"\"\/<\/figure>\n\n\n
We can now compare the new table with the source data and verify that it all looks as expected. <\/p>\n\n\n
\n
\n
\"\"\/<\/figure>\n\n<\/div>\n\n\n
\n
\"\"\/<\/figure>\n\n<\/div>\n\n<\/div>\n\n\n
💡 The parent child tables COULD all be done in one dataflow, but I do not prefer it. I like to ensure my data is going in as expected in chucks<\/p>\n\n\n
Dataflows are a great tool and with a little planning you can make sure that data gets to the correct place and in the correct format.<\/p>\n\n","protected":false},"excerpt":{"rendered":"
Dataflows are a powerful tool for importing large amounts of data from numerous data sources into Dataverse. Being such a powerful tool, certain steps need to be followed to ensure things like lookups and datatypes are handled correctly. The process I use is a very conservative approach as I like to ensure data is correct […]<\/p>\n","protected":false},"author":1,"featured_media":1101,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[48,22],"tags":[],"class_list":["post-1100","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-dataflows","category-dataverse","entry","has-media"],"jetpack_featured_media_url":"https:\/\/automatethemundane.com\/wp-content\/uploads\/2024\/09\/photo-1518837695005-2083093ee35b-scaled.jpg","_links":{"self":[{"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/posts\/1100","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/comments?post=1100"}],"version-history":[{"count":0,"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/posts\/1100\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/media\/1101"}],"wp:attachment":[{"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/media?parent=1100"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/categories?post=1100"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/tags?post=1100"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}