Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Using If to prevent a table displaying

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...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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