Forum Discussion

Frequent Visitor

10 months ago

Solved

Power Query Underlying Problem

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}})

#"Changed Type"

Need Help

v-saisrao-msft

10 months ago

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.

6 Replies

Omid_Motamedise

Super User

10 months ago

you can make your error handling more robust

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

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

Frequent Visitor

10 months ago

Still the same error popping Sir 🙏

v-saisrao-msft

Community Support

10 months ago

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.

v-saisrao-msft
Community Support
9 months ago
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.
Shubham_rai955
Super User
9 months ago
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
Community Support
8 months ago
Hi Klik,

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

Thank you.

Forum Discussion

Power Query Underlying Problem

6 Replies

Quick Fix

Better Approach

Related content

Underlying Error: OnPremiseServiceException

Underlying ErrorOnPremiseServiceException

Logic problem?

Power BI Report Server Scheduled Refresh Bottleneck Problem

Problem with filters in Power Bi service