cancel
Showing results for
Did you mean:
Member

## Power Query Find Amount of Iterations + Dynamic Table

Hi All.
This is a two part question, which I hope is an easy fix.
Part 1 is that I need some query expertise. Let's say I have a list with 5000 records. In query, I would like to see how many phases or iterations my list can be broken into divisible by 1000. So in this case, if we have 5000 records we would have 5 phases or iterations. I definitely can do a long conditional column to achieve my desired result but I'm wondering if there's a more dynamic approach. If you want to follow along here's the query I'm currently using:

```let
Source = {1..5000},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom" = Table.AddColumn(#"Converted to Table", "HowManyPhases", each if [Column1] >= 1 and [Column1] <= 1000 then "Phase 1" else if [Column1] >= 1001 and [Column1] <= 2000 then "Phase 2" else if [Column1] >= 2001 and [Column1] <= 3000 then "Phase 3" else if [Column1] >= 3001 and [Column1] <= 4000 then "Phase 4" else if [Column1] >= 4001 and [Column1] <= 5000 then "Phase 5" else "Last")
in

Part 2 is creating a dynamic table. Using a slicer to determine the phase, I am using SELECTEDVALUE to retrieve it's value. This is the part where I'm stuck. The following formula returns a blank table:

```NewTable =
var mySelection = SELECTEDVALUE(Query1[HowManyPhases])
return
FILTER(SELECTCOLUMNS(Query1, "test", Query1[Column1], "iterations", Query1[HowManyPhases]), [iterations] = mySelection)```

However, when I hardcode which phase I want I am able to return the desired table:

```NewTable =
var mySelection = SELECTEDVALUE(Query1[HowManyPhases])
return
FILTER(SELECTCOLUMNS(Query1, "test", Query1[Column1], "iterations", Query1[HowManyPhases]), [iterations] = mySelection)```

Any ideas as to why selectedvalue isn't working properly?

4 REPLIES 4
Super Contributor

## Re: Power Query Find Amount of Iterations + Dynamic Table

Hi @hnguy71 ,

For the first problem, we can use the following step:

`#"Added Custom" = Table.AddColumn(#"Changed Type", "HowManyPhases", each "Phase " & Text.From(Number.IntegerDivide([Column1],1000)+1))`

For the second problem, you are going to create a dynamic table based on the slicer value, it is not possible, the calculated table and calculated column are calculated when you create them, the content in them will not be changed by the filter, as a result, the function selectedvalue is useless to them.

The method you are using is only useful for measure, because the value of measure will be changed by filter outside.

Best Regards,

Teige

Member

## Re: Power Query Find Amount of Iterations + Dynamic Table

Hi @TeigeGao
Thanks for the query solution! It works pretty **bleep** good.
As for the second issue I still think it's possible to dynamically create a physical table using selected value. My new idea is to have a measure return me the absolute singular value so I can pass that into my table. Here's what I have so far (currently not working):

```FindMe =
var selectMe = SELECTEDVALUE(Query1[HowManyPhases])
var Formula1 = CALCULATE(MAX(Query1[HowManyPhases]), FILTER(ALL(Query1[HowManyPhases]), Query1[HowManyPhases] = selectMe))
var Formula2 = LOOKUPVALUE(Query1[HowManyPhases], Query1[HowManyPhases], SELECTEDVALUE(Query1[HowManyPhases]))
return
Formula2```

My current results look like this:

I turned off interations between the two but my desired result would be to return all Phase 1 within my measure.  I know it's possible but I feel like I'm missing something.

Super Contributor

## Re: Power Query Find Amount of Iterations + Dynamic Table

Hi @hnguy71 ,

Actually, it isn't possible to create a dynamic calculated table. However, this doesn't mean that we can't figure a workaround to solve your problem.

Based on my understanding, you want to use the result of a measure to filter a table, (if I select Phase 1, it will only display the data of Phase 1) please correct if any misunderstand.

This is not hard, drag the HowmanyPhase column to the filter directly:

Best Regards,

Teige

Highlighted
Member

## Re: Power Query Find Amount of Iterations + Dynamic Table

Sorry @TeigeGao ,
I still do want to use the result of a measure to filter a table. If you remove the filtering experience between the two visuals you'll notice that the measure using selectedvalue returns all the phases: 1, 2,3,4, etc.

What I would like the desired measure output to be like this:

 Measure Column1 HowManyPhases Phase 1 700 Phase 1 Phase 1 800 Phase 2 Phase 1 900 Phase 3 Phase 1 100 Phase 4 Phase 1 560 Phase 1 Phase 1 500 Phase 2 Phase 1 780 Phase 3

So when I select Phase 1 within my slicer that's my total output. Hope that cleared things up