Forum Discussion

Klik's avatar
Klik
Frequent Visitor
9 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 repo...
Need Help
  • v-saisrao-msft's avatar
    v-saisrao-msft
    9 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.

Omid_Motamedise's avatar
Omid_Motamedise
Super User
9 months ago

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's avatar
    Klik
    Frequent Visitor
    9 months ago

    Still the same error popping Sir 🙏

    • v-saisrao-msft's avatar
      v-saisrao-msft
      Community Support
      9 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.