Forum Discussion

MichaelF1's avatar
MichaelF1
Helper III
3 years ago

Power Query help please - Refresh error: "Please rebuild this data combination"

Hi, I have suddenly started getting this error:

 

 

I have read this blog: 

https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/

But this webpage states that the problem is caused by combining different data sources - I have only one data source which is a folder of excel files.

 

Here is my code:

 

let
    Source = Folder.Files("S:\Operations\Scheduled Reports\WH083_SLA"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"ATMSDelivReq.DelReqCode", type text}, {"ATMSDelivReq.CreatedDate", type date}, {"ATMSDelivReq.CreatedTime", type time}, {"ATMSDelivReq.EarliestDelivDate", type date}, {"ATMSDelivReq.LatestDelivDate", type date}, {"ATMSDelivReq.OrderType", type text}, {"ATMSDelivReq.RefLine1Class/Order", type text}, {"ATMSDelivReq.RefLine2OrderLine", Int64.Type}, {"ATMSDelivReq.ReqNumberOf", Int64.Type}, {"ATMSDelivReq.PickedSoFar", Int64.Type}, {"Allocated not picked", Int64.Type}, {"ATMSDelivReq.StockAllocSoFar", Int64.Type}, {"ATMSDelivReq.DespatchedSoFar", Int64.Type}}),
    #"Filtered Rows3" = Table.SelectRows(#"Changed Type", each not Text.StartsWith([ATMSCustomerGroup.NACode], "NA6198") or [ATMSCustomerGroup.NACode] <> "NA6199"),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows3",{"Source.Name", "ATMSDelivReq.DelReqCode", "ATMSDelivReq.CreatedDate", "ATMSDelivReq.EarliestDelivDate", "ATMSDelivReq.LatestDelivDate", "ATMSDelivReq.ReqNumberOf", "ATMSDelivReq.StockAllocSoFar", "ATMSDelivReq.PickedSoFar", "ATMSDelivReq.DespatchedSoFar", "ATMSDelivReq.RefLine1Class/Order", "ATMSCustomerGroup.NACode", "ATMSDelivReq.RefLine2OrderLine", "Allocated not picked"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each Text.StartsWith([#"ATMSDelivReq.RefLine1Class/Order"], "SPRE") or Text.StartsWith([#"ATMSDelivReq.RefLine1Class/Order"], "SREC")),
    #"Extracted Text Range" = Table.TransformColumns(#"Filtered Rows", {{"Source.Name", each Text.Middle(_, 10, 8), type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Extracted Text Range",{{"Source.Name", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Source.Name", "ReportDate"}, {"ATMSDelivReq.DelReqCode", "DelReqCode"}, {"ATMSDelivReq.CreatedDate", "CreatedDate"}, {"ATMSDelivReq.EarliestDelivDate", "EarliestDelivDate"}, {"ATMSDelivReq.LatestDelivDate", "LatestDelivDate"}, {"ATMSDelivReq.RefLine1Class/Order", "RefLine1Class/Order"}, {"ATMSDelivReq.ReqNumberOf", "ReqNumberOf"}, {"ATMSDelivReq.PickedSoFar", "PickedSoFar"}, {"ATMSDelivReq.StockAllocSoFar", "StockAllocSoFar"}, {"ATMSDelivReq.DespatchedSoFar", "DespatchedSoFar"}, {"ATMSDelivReq.RefLine2OrderLine", "RefLine2OrderLine"}, {"ATMSCustomerGroup.NACode", "NACode"}}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Renamed Columns", {"RefLine2OrderLine"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Errors", {"ReportDate", "DelReqCode"}),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Removed Duplicates", "Text After Delimiter", each Text.AfterDelimiter([#"RefLine1Class/Order"], "/"), type text),
    #"Renamed Columns2" = Table.RenameColumns(#"Inserted Text After Delimiter",{{"Text After Delimiter", "OrderNo"}, {"RefLine2OrderLine", "OrderLine"}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Renamed Columns2", "Merged", each Text.Combine({[OrderNo], "-", Text.From([OrderLine], "en-GB")}), type text),
    #"Renamed Columns3" = Table.RenameColumns(#"Inserted Merged Column",{{"Merged", "OrderLine-ID"}}),
    #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns3", each [LatestDelivDate] <> null and [LatestDelivDate] > #date(2023, 4, 4) ),
    #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each Duration.Days([LatestDelivDate]-[CreatedDate]) > 1)
in
    #"Filtered Rows2"

 

 

Thanks in advance!

 

 

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi MichaelF1 ,

    Try turning the steps preceding #"Filtered Rows2" into a separate query, as suggested in the blog, and then create a new query to reference that query.

    Each file in the folder should be considered a separate source.

     

    Best Regards,
    Gao

    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum