Using If to prevent a table displaying

[Anonymous]

Hi

I am running a report which raw data is added to every 5 weeks. That data is then manipulated into a cumulative totals table to calculate the number of incidents per person per block to enable comparisons to be made.

let
Source = Excel.CurrentWorkbook(){[Name="Cum_Total"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"UPN", type text}, {"Block", Int64.Type}, {"PP", type text}, {"Year", type text}, {"Count", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Block] = 1 or [Block] = 2)),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"UPN", "PP", "Year"}, {{"Count", each List.Sum([Count]), type number}})
in
#"Grouped Rows"

The code is quiet simple and will be used to complete 8 pre prepared tables.  The #"Filtered Rows" is changed for each table by the addition of another or [Block] = x.

This is fine but when I refresh after Block 1 data has been imported into the Raw Data table all the pre prepared tables populate with the Block 1 data because of the filtered row statement even though data for Blocks 2 to 8 is not available.  This messes up an analysis table which should be empty until the relevant data for the later Blocks is entered.  I need some way of stopping the tables from populating until relevant Blocks data is made available.

Basically you have

Raw Data Table -> Cumulative Total Table (Totals the UPN Value for each Block) -> Block Tables (Cumulative up to each Block i.e Table 1 data up to Block 1, Table 2 data for Block 1 and Block 2 etc

I need to stop Table 2 populating until there is some Block 2 data in the Cumulative Total Table

I am sure it is a lot simpler than I have made it sound and would appreciate any help you could offer

Thank you

[Anonymous]

Thank you for the prompt reply but I am new to this and do not understand the whole process you are suggesting.

The Raw Data table looks like this

UPN        Block

12345         1

12345         1

54321         1

12345         2

23456         2

etc

The first table would look like this

UPN         Block          Count

12345         1                  2

12345         2                  1

54321         1                  1

23456         2                  1

etc

Table after Block 1 would look like this

UPN          Count

12345          2

54321          1

Table after Block 2 would look like this

UPN          Count

12345          3

54321          1

23456          1

Table after Block 3 should be empty but, at present pulss through the contents of table 2.  I need a way of stopping the data from appearing in Block 3 if there is no Block 3 data in the Raw Data table.

I have tried something like this

#"Filtered Rows" = Table.SelectRows(#"Changed Type", each if ([Block] = 1 or [Block] = 2) then ([Block] = 1 or [Block] = 2)  else xxxxxx),

It is what to put at xxxxx that will stop the data from displaying that is the problem.

PS I am using Power Query in Excel not Power BI

[Anonymous · Accepted solution]

I have come up with this.  It seesm to work but doesn't auto refresh.  

Any ideas of a better way to do this?

let
Source = Excel.CurrentWorkbook(){[Name="Cum_Total"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"UPN", type text}, {"Block", Int64.Type}, {"PP", type text}, {"Year", type text}, {"Count", Int64.Type}}),
#"Removed Rows" = Table.Skip(#"Changed Type", each ([Block] = 1 or [Block] = 2 or [Block] = 3 or [Block] = 4 or [Block] = 5)),
#"Filtered Rows" = Table.SelectRows(#"Removed Rows", each ([Block] = 1 or [Block] = 2 or [Block] = 3 or [Block] = 4 or [Block] = 5 or [Block] = 6)),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"UPN", "PP", "Year"}, {{"Count", each List.Sum([Count]), type number}})

in
#"Grouped Rows"

Thanks

[v-frfei-msft]

Hi @Anonymous ,

In your scenario, Query Parameters should help you. Then you can update your code like this

[Block] = [Parameter]