Log In
Register
Home
February 20, 2019
Offline
How can I remove rows only when M1,T1,W1,Th1, and F1 are all "null"?
Trusted Members
October 18, 2018
Offline
Pictures are really nice, but cannot manipulate data in them. Suggest you upload a sample file that we can work with. Don't ask us to recreate your file for you. We may not format the same way and it may result in an incorrect solution. Also, it takes time to do what you already have.
October 27, 2020
Offline
One way would be to create a Custom column (You can call it the "Keeper") with the formula:
if ([M1] = null and [T1] = null and [W1] = null and [Th1] = null and [F1] = null) then "Remove" else "Keep"
Then filter out "Remove" from the "Keeper" column, then remove the "Keeper".
February 20, 2019
Offline
@Greame, The solution above is great. I tested it and it works. Thanks a lot!
@Alan, I attached a zipped file here or you may use Dropbox link below. If you have a better solution, please let me know,
https://www.dropbox.com/s/zpiz.....s.zip?dl=0
Thank you all!
Jim
Trusted Members
October 18, 2018
Offline
1. Once imported to PQE, replace all null with zero
2. Set the data type for the columns in question to whole numbers
3. Add the columns (in a new column) and then filter out any that equal zero.
Note: Once I did the above-in your example there were no rows that were equal to zero, ie blank.
let
Source = Excel.CurrentWorkbook(){[Name="Table_SC_In_Person"]}[Content],
#"Replaced Value" = Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue,{"M1", "T1", "W1", "Th1", "F1", "M2", "T2", "W2", "Th2", "F2", "M3", "T3", "W3", "Th3", "F3", "M4", "T4", "W4", "Th4", "F4", "M5", "T5", "W5", "Th5", "F5", "M6", "T6", "W6", "Th6", "F6", "M7", "T7", "W7", "Th7", "F7", "M8", "T8", "W8", "Th8", "F8", "M9", "T9", "W9", "Th9", "F9", "M10", "T10", "W10", "Th10", "F10"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"M1", Int64.Type}, {"T1", Int64.Type}, {"W1", Int64.Type}, {"Th1", Int64.Type}, {"F1", Int64.Type}, {"M2", Int64.Type}, {"T2", Int64.Type}, {"W2", Int64.Type}, {"Th2", Int64.Type}, {"F2", Int64.Type}, {"M3", Int64.Type}, {"T3", Int64.Type}, {"W3", Int64.Type}, {"Th3", Int64.Type}, {"F3", Int64.Type}, {"M4", Int64.Type}, {"T4", Int64.Type}, {"W4", Int64.Type}, {"Th4", Int64.Type}, {"F4", Int64.Type}, {"M5", Int64.Type}, {"T5", Int64.Type}, {"W5", Int64.Type}, {"Th5", Int64.Type}, {"F5", Int64.Type}, {"M6", Int64.Type}, {"T6", Int64.Type}, {"W6", Int64.Type}, {"Th6", Int64.Type}, {"F6", Int64.Type}, {"M7", Int64.Type}, {"T7", Int64.Type}, {"W7", Int64.Type}, {"Th7", Int64.Type}, {"F7", Int64.Type}, {"M8", Int64.Type}, {"T8", Int64.Type}, {"W8", Int64.Type}, {"Th8", Int64.Type}, {"F8", Int64.Type}, {"M9", Int64.Type}, {"T9", Int64.Type}, {"W9", Int64.Type}, {"Th9", Int64.Type}, {"F9", Int64.Type}, {"M10", Int64.Type}, {"T10", Int64.Type}, {"W10", Int64.Type}, {"Th10", Int64.Type}, {"F10", Int64.Type}}),
#"Inserted Sum" = Table.AddColumn(#"Changed Type", "Addition", each List.Sum({[M1], [T1], [W1], [Th1], [F1], [M2], [T2], [W2], [Th2], [F2], [M3], [T3], [W3], [Th3], [F3], [M4], [T4], [W4], [Th4], [F4], [M5], [T5], [W5], [Th5], [F5], [M6], [T6], [W6], [Th6], [F6], [M7], [T7], [W7], [Th7], [F7], [M8], [T8], [W8], [Th8], [F8], [M9], [T9], [W9], [Th9], [F9], [M10], [T10], [W10], [Th10], [F10]}), Int64.Type),
#"Filtered Rows" = Table.SelectRows(#"Inserted Sum", each true)
in
#"Filtered Rows"
1 Guest(s)
