I have a working query in Power Query editor that pulls back a subscriber table from SQL (approx 750k rows).
I'm trying to Append to this primary query data from an Excel file. It contains select data (columns) on subscribers for the latest period, named consistently with the columns in the Primary table.
The Append query functions as I would expect sometimes. When I refresh, all of the records from the Primary (SQL) query are pulled into Power BI, followed by the Excel records (with null values for the columns not included in the Excel data).
However, I'm constantly running into the issue where the refresh only returns the first 1000 records from the Primary (SQL) query before switching to append the Excel file. No errors are being generated during the refresh--I'm not getting any type mismatch, etc.
Has anyone else run into this? I feel like I'm missing something obvious, but I've checked everything I know to check. Query thread is below--any suggestions appreciated.
PRIMARY (SQL) Query
SyntaxEditor Code Snippet
let
Source = Sql.Database("xxxx\SQLEXPRESS", "201806_census"),
dbo_RosterSnap = Source{[Schema="dbo",Item="RosterSnap"]}[Data],
#"Removed Unnecessary Columns" = Table.RemoveColumns(dbo_RosterSnap,{"xx", "xx", "xx", "xx"}),
#"Filter for Organization" = Table.SelectRows(#"Removed Unnecessary Columns", each [Broker] = "name"),
#"Add ParentSub Key" = Table.AddColumn(#"Filter for Organization", "rsParentSubKey", each Text.Combine({[ParentID],[ProgPartic_Paygroup],[Platform]},",")),
#"Add ParentRevenueCenter Key" = Table.AddColumn(#"Add ParentSub Key", "rsParentRevCtrKey", each Text.Combine({[ParentID],[RevCenter]},",")),
#"Add ParentPlatform Key" = Table.AddColumn(#"Add ParentRevenueCenter Key", "rsParentPlatformKey", each Text.Combine({[ParentID],[Platform]},",")),
#"Add ParentPlatformProviderKey" = Table.AddColumn(#"Add ParentPlatform Key", "rsParentPlatformProviderKey", each Text.Combine({[ParentID],[RevCenter],[MedicalProvider]},",")),
#"Append March TEMP File" = Table.Combine({#"Add ParentPlatformProviderKey", AppendFileforMarchCORE})
in
#"Append March TEMP File"
Append (Excel) Query
SyntaxEditor Code Snippet
let
Source = Excel.Workbook(File.Contents("C:\data\filename.xlsx"), null, true),
MarchData_Table = Source{[Item="MarchData",Kind="Table"]}[Data],
#"Retype Fields to Align with SQL" = Table.TransformColumnTypes(MarchData_Table,{{typechanges}),
#"Add Platform Column" = Table.AddColumn(#"Retype Fields to Align with SQL", "Platform", each "Core"),
#"Add ParentPlatform Key" = Table.AddColumn(#"Add Platform Column", "rsParentPlatformKey", each Text.Combine({[ParentID],[Platform]},",")),
#"Add ParentPlatformProvider Key" = Table.AddColumn(#"Add ParentPlatform Key", "rsParentPlatformProviderKey", each Text.Combine({[ParentID],[Platform],[MedicalProvider]},",")),
#"Add ParentRevCenter Key" = Table.AddColumn(#"Add ParentPlatformProvider Key", "rsParentRevCtrKey", each Text.Combine({[ParentID],[RevCenter]},",")),
#"Add ParentSub Key" = Table.AddColumn(#"Add ParentRevCenter Key", "rsParentSubKey", each Text.Combine({[ParentID],[ProgPartic_Paygroup],[Platform]},",")),
#"Filter for Organization" = Table.SelectRows(#"Add ParentSub Key", each ([Broker] = "name"))
in
#"Filter for Organization"
