topic Re: Issue with Publishing Data in the Lakehouse in Dataflow

Issue with Publishing Data in the Lakehouse

Anonymous — Tue, 24 Sep 2024 08:57:11 GMT

Hello team,

I hope this message finds you well.

I have some data and I have created a script in Power Query. The script runs without any errors and I can see the output in my view.

However, when I try to publish the data in the lakehouse, it's not getting published. Although I receive a pop-up message indicating that the data was published successfully, it remains in refresh mode and never completes the refresh process. I monitored the refresh time and had to cancel it after 1 hour and 30 minutes.

Could you please assist me with this issue?

I have included the script that I've written (without the source details for security reasons).

I look forward to your response and hope to find a solution to this matter.

// Step 2: Ensure the correct data types before sorting ChangeType = Table.TransformColumnTypes(FinalData, { {"SourceAddress", type text}, {"DestinationAddress", type text}, {"StartTime", type datetime}, {"EndTime", type datetime} }), // Step 3: Sort data by Source Address, Destination Address, and Start Time SortedTable = Table.Sort(ChangeType, {{"SourceAddress", Order.Ascending}, {"DestinationAddress", Order.Ascending}, {"StartTime", Order.Ascending}}), // Step 4: Add an Index Column for row reference AddIndex = Table.AddIndexColumn(SortedTable, "Index", 1, 1, Int64.Type), // Step 5: Shift Source and Destination Addresses using List.Generate to efficiently calculate the "NextRow" ShiftedTable = Table.AddColumn(AddIndex, "NextRow", each try SortedTable{[Index]} otherwise null), // Step 6: Calculate Duration using conditional logic AddDurationColumn = Table.AddColumn(ShiftedTable, "Duration", each if [SourceAddress] = [NextRow][SourceAddress] and [DestinationAddress] = [NextRow][DestinationAddress] then Duration.From([NextRow][StartTime] - [EndTime]) else #duration(0, 0, 0, 0), type duration ), // Step 7: Remove unnecessary columns (NextRow and Index) in one step CleanedTable = Table.RemoveColumns(AddDurationColumn, {"NextRow", "Index"}), // Step 8: Add Duration copy column and transform into total seconds AddDurationCopy = Table.AddColumn(CleanedTable, "Duration - Copy", each [Duration], type duration), AddTotalSeconds = Table.TransformColumns(AddDurationCopy, {{"Duration - Copy", Duration.TotalSeconds, type nullable number}}), // Step 9: Rename the original Duration column to "E1 - S2" RenamedColumns = Table.RenameColumns(AddTotalSeconds, {{"Duration", "E1 - S2"}}) in RenamedColumns

Re: Issue with Publishing Data in the Lakehouse

charlyS — Tue, 24 Sep 2024 09:16:14 GMT

Hello Anonymous,

Trying to reproduce, it seems that the last record generate an error retrieving the Duration Column, is there a possibility you didn't saw that ?

Regards,

Re: Issue with Publishing Data in the Lakehouse

Anonymous — Tue, 24 Sep 2024 09:45:46 GMT

Hello charlyS,

I tried, but I didn't get this error. If there were such an error, then this wouldn't even get published.

Re: Issue with Publishing Data in the Lakehouse

Anonymous — Tue, 24 Sep 2024 09:46:21 GMT

Also, I have 40L + records, and in Power Query, I can see only 1000 Records.

Re: Issue with Publishing Data in the Lakehouse

charlyS — Tue, 24 Sep 2024 09:49:02 GMT

Thanks ! Any chance you can provide an exemple as :

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkktLlHSgVFGBkYmugaGQATnGIE4sTrRSiVAJUZAYRgNV4vgGIPVxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SourceAddress = _t, DestinationAddress = _t, StartTime = _t, EndTime = _t]),

// Step 2: Ensure the correct data types before sorting

...

To try to reproduce your error ?

Regards,

Re: Issue with Publishing Data in the Lakehouse

Anonymous — Wed, 25 Sep 2024 02:23:21 GMT

Hi Anonymous

Thank you very much charlyS for your prompt reply.

Although you mentioned that you didn't have the problem that charlyS said you did, I suggest you check. Because in my test, I also encountered the same thing.

This error first occurs in step 5:

// Step 5: Shift Source and Destination Addresses using List.Generate to efficiently calculate the "NextRow" ShiftedTable = Table.AddColumn(AddIndex, "NextRow", each try SortedTable{[Index]} otherwise null),

There should be no "null" here. The result is the error that charlyS describes.

I recommend that you test with a small amount of data first. And modify the step 5 as:

Table.AddColumn(AddIndex, "NextRow", each try SortedTable{[Index] - 1} otherwise null)

Finally, in my tests, it was successfully published in lakehouse.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.