Power Query Underlying Problem

[Klik]

Can anyone assist? 😔

I've been migrating to Power BI Service and have been stuck on this issue for two weeks.

Everything looks fine in preview, no yellow warnings but, when I load it into the report the same error keeps appearing.

The error that i encounter is repeating which is :

Underlying Error Query Evaluation failed for query: 1_TenPlan - RDC_Fab (Unit)

and this is my Query:

let 

Source = SharePoint.Files("https://miscbhd.sharepoint.com/sites/xxxx", [ApiVersion = 15]),

FilteredFiles = Table.SelectRows(Source, each Text.Contains([Name], "ENGINEERING") and Text.EndsWith([Extension], ".xlsx") ),

SortedFiles = Table.Sort(FilteredFiles, {{"Date modified", Order.Descending}}), LatestFile = Table.FirstN(SortedFiles, 1),

ExcelContent = Table.AddColumn(LatestFile, "ExcelData", each Excel.Workbook([Content], false)),

RenamedBeforeExpand = Table.RenameColumns(ExcelContent, {{"Name", "FileName"}}),

Expanded = Table.ExpandTableColumn(RenamedBeforeExpand, "ExcelData", {"Name", "Data"}),

PossibleNames = {"Dashboard","DASHBOARD"}, FilteredSheet = Table.SelectRows(Expanded, each List.Contains(PossibleNames, [Name])),

Extracted = if Table.IsEmpty(FilteredSheet) then #table({}, {}) else FilteredSheet{0}[Data],

#"Removed Top Rows" = Table.Skip(Extracted,4),

#"Removed Other Columns" = Table.SelectColumns(#"Removed Top Rows",{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14"}),

#"Promoted Headers" = Table.PromoteHeaders(#"Removed Other Columns", [PromoteAllScalars=true]),

#"Removed Top Rows1" = Table.Skip(#"Promoted Headers",1), #"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Top Rows1",2),

#"Changed Type" = Table.TransformColumnTypes(#"Removed Bottom Rows",{{"TOTAL", Int64.Type}, {"%#(lf)PLAN ", Percentage.Type}, {"%#(lf)ACTUAL ", Percentage.Type}, {"%#(lf)VAR. ", Percentage.Type}, {"%#(lf)BAL.", Percentage.Type}})

in

#"Changed Type"

[v-saisrao-msft]

Hi @Klik,

Checking in to see if your issue has been resolved. let us know if you still need any assistance.

Thank you.

[Shubham_rai955]

The error happens because Table.RemoveLastN (and similar row removal steps) fails in Power BI Service when the Excel sheet has fewer rows than expected after filtering/skipping. Preview works since your local file matches expectations, but the latest SharePoint file likely has different row count.​

Quick Fix

Replace the failing steps with safer versions:

#"Removed Top Rows1" = Table.Skip(#"Promoted Headers",1), #"Removed Bottom Rows" = if Table.RowCount(#"Removed Top Rows1") > 3 then Table.RemoveLastN(#"Removed Top Rows1",2) else #"Removed Top Rows1",

This checks row count before removing.​​

Better Approach

Add Table.Buffer(Extracted) right after the Extracted step to lock data in memory and prevent folding issues during service refresh. Also verify the latest ENGINEERING.xlsx file on SharePoint has the expected "Dashboard"/"DASHBOARD" sheet with enough rows (>7 total after skips). Test by manually refreshing each step in Power Query Editor.​

[v-saisrao-msft]

Hi @Klik,

Have you had a chance to review the solution we shared earlier? If the issue persists, feel free to reply so we can help further.

Thank you.

[Omid_Motamedise]

you can make your error handling more robust

Extracted = if Table.IsEmpty(FilteredSheet) then #table({}, {}) else FilteredSheet{0}[Data],

or

Extracted =
if Table.IsEmpty(FilteredSheet) then
    #table(type table [], {})
else
    try FilteredSheet{0}[Data] otherwise #table(type table [], {})

This ensures that if the workbook changes or is missing the “Dashboard” sheet, it returns a blank table instead of crashing.

[Klik]

Still the same error popping Sir 🙏

[v-saisrao-msft · Accepted solution]

Hi @Klik,

Please try the updated M-Query Below

let
    Source = SharePoint.Contents("https://miscbhd.sharepoint.com/sites/xxxx", [ApiVersion = 15]),
    Library = Source{[Name="Shared Documents"]}[Content],
    FilteredFiles = Table.SelectRows(Library, each Text.Contains([Name], "ENGINEERING") and Text.EndsWith([Name], ".xlsx")), 
    SortedFiles = Table.Sort(FilteredFiles, {{"Date modified", Order.Descending}}),
    LatestFile = Table.FirstN(SortedFiles, 1),   
    Binary = LatestFile{0}[Content],
    ExcelContent = Excel.Workbook(Binary, false),
    PossibleNames = {"Dashboard", "DASHBOARD"},
    FilteredSheet = Table.SelectRows(ExcelContent, each List.Contains(PossibleNames, [Name])), 
    Extracted = if Table.IsEmpty(FilteredSheet) then #table({}, {}) else FilteredSheet{0}[Data],
    #"Removed Top Rows" = Table.Skip(Extracted, 4),
    #"Removed Other Columns" = Table.SelectColumns(#"Removed Top Rows", {
        "Column1", "Column2", "Column3", "Column4", "Column5", 
        "Column6", "Column7", "Column8", "Column9", "Column10", 
        "Column11", "Column12", "Column13", "Column14"
    }),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Other Columns", [PromoteAllScalars = true]),
    #"Removed Top Rows1" = Table.Skip(#"Promoted Headers", 1),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Top Rows1", 2),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Bottom Rows", {
        {"%#(lf)PLAN ", "PLAN"},
        {"%#(lf)ACTUAL ", "ACTUAL"},
        {"%#(lf)VAR. ", "VAR"},
        {"%#(lf)BAL.", "BAL"},
        {"TOTAL", "TOTAL"}
    }, MissingField.Ignore),


    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {
        {"TOTAL", Int64.Type}, 
        {"PLAN", Percentage.Type}, 
        {"ACTUAL", Percentage.Type}, 
        {"VAR", Percentage.Type}, 
        {"BAL", Percentage.Type}
    })
in
    #"Changed Type"

Thank you.