Ufdjrw

Chebyshev inequality in terms of RMS

If Windows 7 doesn't support WSL, then what does Linux subsystem option mean?

Most bit efficient text communication method?

Is there a kind of relay only consumes power when switching?

Has negative voting ever been officially implemented in elections, or seriously proposed, or even studied?

AppleTVs create a chatty alternate WiFi network

An adverb for when you're not exaggerating

Why wasn't DOSKEY integrated with COMMAND.COM?

ArcGIS Pro Python arcpy.CreatePersonalGDB_management

How does the math work when buying airline miles?

Why do we bend a book to keep it straight?

Illegal assignment from sObject to Id

Why is Nikon 1.4g better when Nikon 1.8g is sharper?

Do wooden building fires get hotter than 600°C?

Using audio cues to encourage good posture

Maximum summed subsequences with non-adjacent items

Putting class ranking in CV, but against dept guidelines

Why is my ESD wriststrap failing with nitrile gloves on?

How much damage would a cupful of neutron star matter do to the Earth?

What would you call this weird metallic apparatus that allows you to lift people?

What is the appropriate index architecture when forced to implement IsDeleted (soft deletes)?

Hangman Game with C++

Generate an RGB colour grid

How were pictures turned from film to a big picture in a picture frame before digital scanning?

Power Query to Filter a SQL view based on an Excel column list

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

1

0

Is there a way to filter a SQL view based on a list of values in an excel table column using Power Query?

I have a SQL view that returns a large set of data (millions of records or properties). Users want to filter that based on an excel table column of property IDs. I know I can just do a merge join based on the property ID between the view and the excel column in power query. But it looks like the merge brings in the millions of records first then filtered it in the join. Which takes a long time. Users want to change the list of propertyIDs on the fly on a daily basis and run the query.

Essentially, I wanted to create in Excel power query
what is in SQL

SELECT * FROM SQLViewName 
WHERE PropertyID IN (Select Column from ExcelTable) 

sql-server excel powerbi powerquery m

share|improve this question

edited Mar 10 at 0:02

Alexis Olson

15.7k22136

asked Mar 8 at 20:19

stevenjmyustevenjmyu

4573927

add a comment | 

1

0

Is there a way to filter a SQL view based on a list of values in an excel table column using Power Query?

I have a SQL view that returns a large set of data (millions of records or properties). Users want to filter that based on an excel table column of property IDs. I know I can just do a merge join based on the property ID between the view and the excel column in power query. But it looks like the merge brings in the millions of records first then filtered it in the join. Which takes a long time. Users want to change the list of propertyIDs on the fly on a daily basis and run the query.

Essentially, I wanted to create in Excel power query
what is in SQL

SELECT * FROM SQLViewName 
WHERE PropertyID IN (Select Column from ExcelTable) 

sql-server excel powerbi powerquery m

share|improve this question

edited Mar 10 at 0:02

Alexis Olson

15.7k22136

asked Mar 8 at 20:19

stevenjmyustevenjmyu

4573927

add a comment | 

Is there a way to filter a SQL view based on a list of values in an excel table column using Power Query?

I have a SQL view that returns a large set of data (millions of records or properties). Users want to filter that based on an excel table column of property IDs. I know I can just do a merge join based on the property ID between the view and the excel column in power query. But it looks like the merge brings in the millions of records first then filtered it in the join. Which takes a long time. Users want to change the list of propertyIDs on the fly on a daily basis and run the query.

Essentially, I wanted to create in Excel power query
what is in SQL

SELECT * FROM SQLViewName 
WHERE PropertyID IN (Select Column from ExcelTable) 

sql-server excel powerbi powerquery m

share|improve this question

edited Mar 10 at 0:02

Alexis Olson

15.7k22136

asked Mar 8 at 20:19

stevenjmyustevenjmyu

4573927

SELECT * FROM SQLViewName 
WHERE PropertyID IN (Select Column from ExcelTable) 

sql-server excel powerbi powerquery m

sql-server excel powerbi powerquery m

share|improve this question

edited Mar 10 at 0:02

Alexis Olson

15.7k22136

asked Mar 8 at 20:19

stevenjmyustevenjmyu

4573927

share|improve this question

edited Mar 10 at 0:02

Alexis Olson

15.7k22136

asked Mar 8 at 20:19

stevenjmyustevenjmyu

4573927

edited Mar 10 at 0:02

Alexis Olson

15.7k22136

edited Mar 10 at 0:02

Alexis Olson

15.7k22136

asked Mar 8 at 20:19

stevenjmyustevenjmyu

4573927

asked Mar 8 at 20:19

stevenjmyustevenjmyu

4573927

1 Answer
1

active

oldest

votes

2

You should be able to do this with a List.Contains function.

If my ExcelTable is

ID
---
436
437
438
439

then adding a filter like this should do the trick:

Table.SelectRows(SQLViewName, each List.Contains(ExcelTable[ID], [PropertyID]))

---

When I tried this and did View Native Query on the last applied step, it folded the Excel table into a WHERE clause with the ExcelTable values as literals like this:

select [_].[PropertyID],
 [_].[OtherColumns]
from [dbo].[SQLViewName] as [_]
where [_].[PropertyID] in (436, 437, 438, 439)

This allowed me to load a multi-million-row table in just a couple seconds.

share|improve this answer

answered Mar 10 at 20:36

Alexis OlsonAlexis Olson

15.7k22136

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55070454%2fpower-query-to-filter-a-sql-view-based-on-an-excel-column-list%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

2

You should be able to do this with a List.Contains function.

If my ExcelTable is

ID
---
436
437
438
439

then adding a filter like this should do the trick:

Table.SelectRows(SQLViewName, each List.Contains(ExcelTable[ID], [PropertyID]))

---

When I tried this and did View Native Query on the last applied step, it folded the Excel table into a WHERE clause with the ExcelTable values as literals like this:

select [_].[PropertyID],
 [_].[OtherColumns]
from [dbo].[SQLViewName] as [_]
where [_].[PropertyID] in (436, 437, 438, 439)

This allowed me to load a multi-million-row table in just a couple seconds.

share|improve this answer

answered Mar 10 at 20:36

Alexis OlsonAlexis Olson

15.7k22136

add a comment | 

2

You should be able to do this with a List.Contains function.

If my ExcelTable is

ID
---
436
437
438
439

then adding a filter like this should do the trick:

Table.SelectRows(SQLViewName, each List.Contains(ExcelTable[ID], [PropertyID]))

---

When I tried this and did View Native Query on the last applied step, it folded the Excel table into a WHERE clause with the ExcelTable values as literals like this:

select [_].[PropertyID],
 [_].[OtherColumns]
from [dbo].[SQLViewName] as [_]
where [_].[PropertyID] in (436, 437, 438, 439)

This allowed me to load a multi-million-row table in just a couple seconds.

share|improve this answer

answered Mar 10 at 20:36

Alexis OlsonAlexis Olson

15.7k22136

add a comment | 

You should be able to do this with a List.Contains function.

If my ExcelTable is

ID
---
436
437
438
439

then adding a filter like this should do the trick:

Table.SelectRows(SQLViewName, each List.Contains(ExcelTable[ID], [PropertyID]))

---

When I tried this and did View Native Query on the last applied step, it folded the Excel table into a WHERE clause with the ExcelTable values as literals like this:

select [_].[PropertyID],
 [_].[OtherColumns]
from [dbo].[SQLViewName] as [_]
where [_].[PropertyID] in (436, 437, 438, 439)

This allowed me to load a multi-million-row table in just a couple seconds.

share|improve this answer

answered Mar 10 at 20:36

Alexis OlsonAlexis Olson

15.7k22136

ID
---
436
437
438
439

Table.SelectRows(SQLViewName, each List.Contains(ExcelTable[ID], [PropertyID]))

select [_].[PropertyID],
 [_].[OtherColumns]
from [dbo].[SQLViewName] as [_]
where [_].[PropertyID] in (436, 437, 438, 439)

share|improve this answer

answered Mar 10 at 20:36

Alexis OlsonAlexis Olson

15.7k22136

answered Mar 10 at 20:36

Alexis OlsonAlexis Olson

15.7k22136

answered Mar 10 at 20:36

Alexis OlsonAlexis Olson

15.7k22136