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 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

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

3 Replies

  • 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