Edit Query function for breaking up a SQL query automatically

[Anonymous]

Hi,

I have a question and was wondering if Power BI has this ability:

Lets pretend that I have a SQL Query that I'm running and a user defined parameter.  Here is an example of the SQL Query table results:

Group     BenCode     PlanPaid

001          R32            $700

001          Z11            $50

004          R32            $600

007          AB5            $400

001          FMD           $60

Let's assume the Parameter I mentioned earlier dictates what Group the query populates with.  So for example, if the user entered "001" the query results would look something like this:

Group     BenCode     PlanPaid

001          R32            $700

001          Z11            $50

001          FMD           $60

My question is:  Is there the ability to have these results broken out in a way to have an individual table made for each BenCode automatically?  For example I would want the following tables created:

BenCode

R32

BenCode

Z11

BenCode

FMD

I would need this last step automated because in theory I want the user to be able to enter in the Group they want to query for within the parameter I mentioned earlier and have these 'Passthrough' tables automatically created.  The reason why is because each group has a different batch of BenCodes.  Creating it in this way keeps the Power BI Report manageable in size.

Any ideas?

[Anonymous]

Hi @Anonymous 

Has your problem been solved ? If no, please provide your sample and the result you want .

If it has been solved, provide your method for the problem then consider Accept it as the solution to help the other members find it more quickly.

Best Regards

Community Support Team _ Ailsa Tao

[Anonymous · Accepted solution]

Hi @Anonymous 

You can create a slicer with field ‘Group’, and then filter the data with slicer .

As for what you said ,when the user enter the group , 'Passthrough' tables will be created automatically .I have a question that if there are a few hundred categories for ‘BenCode’ , then you have to build hundreds of tables separately ? Such an operation will increase the size of the report .

So I don’t particularly understand your purpose for doing this .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , I would suggest to have a single source of truth with all data . 

See if Dynamics M parameter wth direct query can help you

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters

or this way

https://www.thebiccountant.com/2017/03/22/use-slicers-for-query-parameters-in-powerbi/