Power Query: not Text.StartsWith removes blank/null values

ko1967

New Member
Joined
Jan 3, 2017
Messages
27
In Power Query I'm trying to filter out and total rows from a data set. Total rows are designated as "Total ..." in [Column1]. So I used the drop down "Text Filter > Does Not Begin With..." which places a "not Text.StartsWith([Column1], "Total") in the code. The problem is this not only filter out where [Column1] starts with "Total" but also filters out null values. This doesn't at all make sense as a null value does not begin with "Total". Does anyone have any insight into this issue and have a solution other than replacing null w/ "zzzzzzzzzz" then filtering not starts with "Total" then replacing "zzzzzzzzzz" with null.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Result = Table.SelectRows(Source, each (Text.Start(Text.From([Month]), 5) <> "Total"))
in
    Result

Book2
ABCDEF
1MonthValueMonthValue
2112112
31919
4116116
51515
6Total for Month 142
721
82128
928217
10217
11Total for Month 22633
12314
1333313
1431432
15313315
163237
17315
1837
19Total for Month 354
20
Sheet2
Cell Formulas
RangeFormula
B6B6=SUM(B2:B5)
B11B11=SUM(B8:B10)
B19B19=SUM(B13:B18)
 
Upvote 0

Forum statistics

Threads
1,223,360
Messages
6,171,631
Members
452,411
Latest member
sprichwort

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top