Power Query reporting non-existent errors?

macfuller

Active Member
Joined
Apr 30, 2014
Messages
317
Office Version
  1. 365
Platform
  1. Windows
My employer recently upgraded to Office 2016. I'm getting an odd result in PowerQuery. When I update a particular query from a macro file using

ActiveWorkbook.Connections("Query - Financing").Refresh

The connection window tells me that every row - 190 - has errors. But when I click on the errors hyperlink the error table is empty.

When I update the query by clicking the Refresh icon in the connection window no errors are reported.

The query is one of 8 in the workbook, 5 of which are drawn from tables within the workbook. None of the other internal source queries displays this behavior. It's inconvenient to have to click on the refresh icon after the macro runs and get this right, but certainly not major.

Here is the code for the Financing query in case anyone has encountered this issue. It merges 4 internal tables as we have 3 loans and a bond as part of our financing. The loans have different time periods and amounts so the query is intended to merge everything into a single table for a total spend in a particular month.

Code:
let
    Source = Table.NestedJoin(tblLoanA,{"Period"},tblLoanB,{"Period"},"NewColumn",JoinKind.LeftOuter),
    ExpandLoanABColumns = Table.ExpandTableColumn(Source, "NewColumn", {"Total Payment", "Total Principal", "Total Interest"}, {"Payment.1", "Principal.1", "Interest.1"}),
    RenameLoanABColumns = Table.RenameColumns(ExpandLoanABColumns,{{"Total Payment", "Construction Payment"}, {"Total Principal", "Construction Principal"}, {"Total Interest", "Construction Interest"}, {"Payment.1", "Equipment Payment"}, {"Principal.1", "Equipment Principal"}, {"Interest.1", "Equipment Interest"}}),
    MergeLoanC = Table.NestedJoin(RenameLoanABColumns,{"Period"},tblLoanC,{"Period"},"NewColumn",JoinKind.LeftOuter),
    ExpandLoanColumns = Table.ExpandTableColumn(MergeLoanC, "NewColumn", {"Total Payment", "Total Principal", "Total Interest"}, {"Payment", "Principal", "Interest"}),
    RenameLoanCColumns = Table.RenameColumns(ExpandLoanColumns,{{"Payment", "Expanded Payment"}, {"Principal", "Expanded Principal"}, {"Interest", "Expanded Interest"}}),
    MergeBond1 = Table.NestedJoin(RenameLoanCColumns,{"Period"},tblBond1,{"Period"},"NewColumn",JoinKind.LeftOuter),
    ExpandBond1Cols = Table.ExpandTableColumn(MergeBond1, "NewColumn", {"Bond A Interest", "Bond A Principal"}, {"Bond A Interest", "Bond A Principal"}),
    MergeCredits = Table.NestedJoin(ExpandBond1Cols,{"Period"},tblCredit,{"Period"},"NewColumn",JoinKind.LeftOuter),
    ExpandCreditCols = Table.ExpandTableColumn(MergeCredits, "NewColumn", {"Principal Credit", "General Credit", "Taxable Income", "Net Credit"}, {"Principal Credit", "General Credit", "Taxable Income", "Net Credit"}),
    ReplaceNulls = Table.ReplaceValue(ExpandCreditCols,null,0,Replacer.ReplaceValue,{"Construction Payment", "Construction Principal", "Construction Interest", "Equipment Payment", "Equipment Principal", "Equipment Interest", "Expanded Payment", "Expanded Principal", "Expanded Interest", "Bond A Principal", "Bond A Interest", "Principal Credit", "General Credit", "Taxable Income", "Net Credit"}),
    AddNetPaymentsCol = Table.AddColumn(ReplaceNulls, "Net Payment", each [Construction Payment] + [Equipment Payment] + [Expanded Payment] + [Bond A Principal] + [Bond A Interest] - [Net Credit], Currency.Type),
    AddTotalPrincipalCol = Table.AddColumn(AddNetPaymentsCol, "Total Principal", each [Construction Principal] + [Equipment Principal] + [Expanded Principal] + [Bond A Principal] - [Principal Credit], Currency.Type),
    AddTotalInterestCol = Table.AddColumn(AddTotalPrincipalCol, "Total Interest", each [Construction Interest] + [Equipment Interest] + [Bond A Interest] + [Expanded Interest], Currency.Type),
    RemoveDistinctCols = Table.RemoveColumns(AddTotalInterestCol,{"Construction Payment", "Construction Principal", "Construction Interest", "Equipment Payment", "Equipment Principal", "Equipment Interest", "Expanded Payment", "Expanded Principal", "Expanded Interest", "Principal Credit", "General Credit", "Net Credit"}),
    ChangePeriodColType = Table.TransformColumnTypes(RemoveDistinctCols,{"Period", type date})
in
    ChangePeriodColType

The loan tables themselves are brought into Power Query through connection-only queries that are pretty similar. Here is the query for Loan A. Because there are points and fees as well as interest and principal payments in the first month each loan and bond query groups the payments by month so that when they're joined in the Finance query there's a straight 1:1 match of months.

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="tblLoanA"]}[Content],
    ChangePeriodToDate = Table.TransformColumnTypes(Source,{{"Period", type datetime}, {"Beginning Balance", type number}, {"Payment", type number}, {"Principal", type number}, {"Interest", type number}, {"Cum. Principal", type number}, {"Cum. Interest", type number}, {"Ending Balance", type number}}),
    GroupByPeriod = Table.Group(ChangePeriodToDate, {"Period"}, {{"Total Payment", each List.Sum([Payment]), type number}, {"Total Principal", each List.Sum([Principal]), type number}, {"Total Interest", each List.Sum([Interest]), type number}})
in
    GroupByPeriod
 
Last edited:

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top