Issue with Publishing Data in the Lakehouse

[Devanshu]

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

[charlyS · Accepted solution]

Hello @Devanshu,

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,

[charlyS · Accepted solution]

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,

[Anonymous · Accepted solution]

Hi @Devanshu 

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.

[charlyS · Accepted solution]

Hello @Devanshu,

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,

[Devanshu]

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

[Devanshu]

Hello @charlyS,

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

[charlyS · Accepted solution]

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,