cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
swbeach001 New Member
New Member

Inconsistent results with Power Query append

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"

 

1 REPLY 1
Moderator v-yuezhe-msft
Moderator

Re: Inconsistent results with Power Query append

@swbeach001,

Could you please describe more details about "the refresh only returns the first 1000 records from the Primary (SQL) query before switching to append the Excel file"? How do you determine that only 1000 records are imported?

Also do you use Power BI Desktop July release? How about you use UNION() function instead to append tables in Power BI Desktop?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.