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.
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.
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: