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

power query

I have multiple tables I created using the Power Query tool.  All tables are based on data from a "Master Dataset".  I would like to be able to filter the Master Dataset using a scenario slicer and have all other tables in the workbook recalculate based on that filter.  

 

Thanks for any help on this!

1 ACCEPTED SOLUTION

Accepted Solutions
horseyride Frequent Visitor
Frequent Visitor

Re: power query

Assuming your input is in a table format

 

(1) Convert "Master Dataset" into a table Insert...table...

(2) Click in table then Insert...Slicer...to add slicers; don't make any selections yet

(3) Add additional formula in blank column of table :

=(AGGREGATE(3, 5, [@Sales])>0)+0

replacing "Sales" with appropriate header row from your table

(3) Create powerquery based on that table, including the extra column

(4) add PowerQuery  filter that removes rows where extra column equals 1.  Those are the hidden rows that you filtered out of dataset. Example:

    #"Filtered Rows" = Table.SelectRows(#"Previous Step", each ([Column1] = 1))

(5) Base your output tables on the filtered input

(6) After changing any slicer values, re-caclcuate PowerQuery to reflect new data

2 REPLIES 2
horseyride Frequent Visitor
Frequent Visitor

Re: power query

Assuming your input is in a table format

 

(1) Convert "Master Dataset" into a table Insert...table...

(2) Click in table then Insert...Slicer...to add slicers; don't make any selections yet

(3) Add additional formula in blank column of table :

=(AGGREGATE(3, 5, [@Sales])>0)+0

replacing "Sales" with appropriate header row from your table

(3) Create powerquery based on that table, including the extra column

(4) add PowerQuery  filter that removes rows where extra column equals 1.  Those are the hidden rows that you filtered out of dataset. Example:

    #"Filtered Rows" = Table.SelectRows(#"Previous Step", each ([Column1] = 1))

(5) Base your output tables on the filtered input

(6) After changing any slicer values, re-caclcuate PowerQuery to reflect new data

Scott94 Frequent Visitor
Frequent Visitor

Re: power query

Thank you so much for your answer! This worked great!

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 368 members 3,826 guests
Please welcome our newest community members: