cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Query to source with parameter from another table (Account IDs)

Hi

 

Hope anybody can help me and my question is reasonable.

 

In Power Query I connect to a new source with below string. The SQL string within includes here 3 Account IDs (RC Number).

But I do not want to have fixed accounts in the query. The query should be flexible and pull the accounts from another table within power query. In addition the number of accounts might be different (not 3 always but 5 accounts) which I query.

 

Original Query

 

 

 

 

= OleDb.DataSource("provider=MSOLAP.8;...", [Query="SELECT {[Measures].[Sell Out Quantity],[Measures].[Sell Out Amount]} ON COLUMNS , NON Empty  Hierarchize({DrilldownLevel({[RC Account].[RC Number].&[0000159985],[RC Account].[RC Number].&[0000199622],[RC Account].[RC Number].&[0000283464]})}) ON ROWS  FROM  (SELECT ({[Currency].[Currency].&[EUR]}) ON COLUMNS FROM [Data-CUBE])  WHERE ({[_Empty Cube].[Empty Cube].&[No]},{[Sell Out Data].[Data Source].&[SellOutFiles]},{[Sell Out Data].[Sales Doubled].&[N/A],[Sell Out Data].[Sales Doubled].&[Not Sales Doubled]},{[Sell Out Data].[Sales Type].&[N/A],[Sell Out Data].[Sales Type].&[Sales]},{[Sell Out File Data].[Flat File Type].&[],[Sell Out File Data].[Flat File Type].&[QUO],[Sell Out File Data].[Flat File Type].&[SAL]}) CELL PROPERTIES VALUE"])

 

 

 

 

 

Example for table which holds the accounts to be used in above query and might change with a refresh.

Account NameAccount number
Ben0000123456
Luke0000443399
Fritz0000121112

 

Thank you so much for any input and support.

Br/Robert

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Query to source with parameter from another table (Account IDs)

1 REPLY 1
Highlighted
Super User III
Super User III

Re: Query to source with parameter from another table (Account IDs)

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors