topic Re: Dataflow Gen 2 - SharePoint List - Target in Dataflow

Dataflow Gen 2 - SharePoint List - Target

pasccout — Fri, 02 Jun 2023 12:56:51 GMT

Hi,

I am trying to get a SharePoint List copied through a DataFlow Gen 2 to OneLake (would like to eventually expose it to Power BI and CoPilot along side other datalake tables).

I have the following issues with the target.

- Upon first execution, the target name is a file name and not the table name that I specified

- The columns in the target become generic even if named in the flow (Column 1, Column 2...)

- Rerunning a flow ignores the previous target and always generates a new one (even if I change it to point to the existing file/table)

Anything I am missing in the configuration of the flow?

Thanks

Re: Dataflow Gen 2 - SharePoint List - Target

jwelch — Fri, 02 Jun 2023 15:46:31 GMT

Can you share a screenshot of the Output Destination configuration?

Re: Dataflow Gen 2 - SharePoint List - Target

pasccout — Fri, 02 Jun 2023 15:51:52 GMT

Is this what you are looking for?

Re: Dataflow Gen 2 - SharePoint List - Target

jwelch — Fri, 02 Jun 2023 16:01:16 GMT

Yes - thanks. I'll take a look to see what's going on.

Re: Dataflow Gen 2 - SharePoint List - Target

jwelch — Fri, 02 Jun 2023 16:41:05 GMT

I haven't been able to reproduce this issue. One note - there is some post processing being done on the Lakehouse after the dataflow refresh completes. It's possible that the processing is taking longer than the norm on your workspace, and that's leading to inconsistencies with what you are seeing. However, I don't think that would be a root cause for this. I'd recommend opening a support ticket for this so we can investigate the specifics of what you are seeing.

Re: Dataflow Gen 2 - SharePoint List - Target

pasccout — Fri, 02 Jun 2023 17:01:27 GMT

Thanks. Let me try it again and I will wait a few minutes for post-processing. Will let you know.

Re: Dataflow Gen 2 - SharePoint List - Target

miguel — Fri, 02 Jun 2023 17:13:15 GMT

Hi!

Could you please share a screenshot of how your table schema looks like for the query that has the output destination definition and another screenshot fo what the column mapping wth the destination looks like?

Re: Dataflow Gen 2 - SharePoint List - Target

pasccout — Fri, 02 Jun 2023 17:27:15 GMT

After the initial execution.. this is in the lakehouse.

After 1 minute or so... I have the file and the table.

Waited 15 minutes... same thing.

Checked the refresh time in the mode file and got "2023-06-02T17:07:17.5296454+00:00"

Triggered the refresh of the dataflow.

Succesfull

Right after, have 3 tables (files)

THe model file has no 2 JSON in it with the second one having the following refresh.

Is this the expected behavior and does that mean that my table/file has been updated?

Does the system keep the other files as a backup or are those removed after a while?

Thanks

Re: Dataflow Gen 2 - SharePoint List - Target

miguel — Fri, 02 Jun 2023 22:38:55 GMT

based on the screenshots, it appears that what you're looking at is what we call a Staging Lakehouse which is not meant to be accessed by the end-user. This is a system-level artifact that is used by Dataflows to help you with the loading of the data to the Lakehouse in your workspace.

We do not recommend interacting with staging artifacts and we will be hiding these artifacts in the future as well as preventing changes from the user. These are only meant to be used by the system.

You should be able to see a different Lakehouse which should be the one that you initially created, but it cannot be either of the Staging ones. it should also say that is owned by you. Can you confirm that you can see such Lakehouse?

Also, please let us know if you can share the screenshots that I mentioned in my previous reply from the Dataflows experience around the schema for your query (just a screenshot of what the columns look like) and the "mapping columns" dialog in the output destinations dialog that also showcases the name of what should be the new table.

Re: Dataflow Gen 2 - SharePoint List - Target

jwelch — Fri, 02 Jun 2023 23:01:42 GMT

Yes, @miguel is correct. The tables named with GUIDs are the results of dataflow staging in the Staging Lakehouse, not the output destination you defined.

Re: Dataflow Gen 2 - SharePoint List - Target

pasccout — Mon, 05 Jun 2023 11:13:41 GMT

Hi,

Thanks for the feedback. FYI. I deleted everything and did the following.

- Created a new data lakehouse

- Created a new flow v2

As for the schema, really simple. I am reading a SP list and loading 2 columns for now: ID & Title. Use the detect type option, set the ID as the key.

Target is now my lakehouse.

And now I see the table in my Lakehouse

Now if I refresh it. It is succesful.

One suggestion for your system. Would it be possible to add a last refresh date & time in the Properties menu option under the Explorer?

Your answers were really useful.

Pascal

Re: Dataflow Gen 2 - SharePoint List - Target

jwelch — Mon, 05 Jun 2023 22:44:20 GMT

Glad it was helpful.

I like the idea of the Last Refresh time. @miguel - do you know which Ideas forum (Home (microsoft.com)) is used for Lakehouse?

-John

Re: Dataflow Gen 2 - SharePoint List - Target

miguel — Mon, 05 Jun 2023 23:09:44 GMT

Hey!

From the products dropdown you can choose the "Synapse" option for ideas around Data Warehouse, Notebooks and Lakehouse.