Challenging for me: How do you mark the Start and End of values for each column

[roncruiser]

I've been struggling with this for some time.  I'm not sure how to approach this one.
For each XRef, I need to mark the beginning and end of the data for each column by adding a "FFFF".
(I've truncated the data for simplicity)

This is where I'm stuck now.

Below is an example of the expected results.  "FFFF" added to the beginning and end for the list of values in each column
for each XRef:

The .pbix contains the M code for power query.  (The M code shown in the first screen capture.)

Download_pbix 

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZZLcqwwDEX3wrgHWDYGD9Mh3kSq97+NIDngnyT7VV53FecUYF9d6N/fxSyvxV3/0996/bu/Pq9EN5V6le4qPVQaVGpWHRsdw/UR44lri9sZJM3ifWwH3uhpMm81Rx/rQfdFX6y2Za3krYZburobiGfb57QjXbS9WquFdLbBvcFanU26KJhqpaIGcxql4Mx7oGEKwYeRhimEPUY9LMAUYtJOb6Kk/U/4GaOo6GMO+pxbec5x9+TqIpWri1SuLlK5ukjlNSGVl0SJ60ui6l7beeYvVgPa9tNQQGyFSUvDQ+DkBztN4bAmpG1V6UTNz2n7nHbMaWFKowpPaGZOg2X03CDNJu1roKUKDzWqsN+UppNWVDh4eEvaXeGdrTAp+rgrFUasVBi3Ra4wUrnCSOUKI5UrjFReE1VGpUqFCacKp9crX2HSqMJ76ubGxk0afaRRNPxUJIPnrbbxvNXGFSZtXGHShH60WvsU4zUQeKuZOQ34GrWaTUeUbpLm5jRM4dtZpcKk+eUppoHupI92V9izFSZFH/fBWziP+zXOeLH4vq70/Hgq6A+uP4ZLel7wD/VvOrMPzuf3+0OBua6tfsZCh0HHtsS+w+WTh1ZFJ3nwVtz4va824/zsQeN6zma0lygfPvjDgT3s1u4wcEnk91+Zwn9Ge35GF4txVQpQp2DqFEBPAfQUoE3B+A7nFKBJAeoUfu7fmBAzblKIBepTAD4F4FMAPgUrpmCbFOqm2CqFGL8dpeD3h+aN8qGvaP0ahQ5XIfkOg465kCDjHJKzdUi2DgnRl80rFgKyfECWD8jyAVkmoM8f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [XRef = _t, TRef = _t, VALUE0000 = _t, VALUE0010 = _t, VALUE0001 = _t, VALUE0011 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"XRef", Int64.Type}, {"TRef", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"XRef", Order.Ascending}, {"TRef", Order.Ascending}}),
    #"Replaced Value" = Table.ReplaceValue(#"Sorted Rows",each [VALUE0000],(k)=> let t=Table.SelectRows(#"Sorted Rows",each [XRef]=k[XRef] and [VALUE0000]<>""),
min = List.Min(t[TRef])-1,
max = List.Max(t[TRef])+1
in if k[TRef]=min or k[TRef]=max then "FFFF" else k[VALUE0000] ,Replacer.ReplaceValue,{"VALUE0000"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each [VALUE0010],(k)=> let t=Table.SelectRows(#"Sorted Rows",each [XRef]=k[XRef] and [VALUE0010]<>""),
min = List.Min(t[TRef])-1,
max = List.Max(t[TRef])+1
in if k[TRef]=min or k[TRef]=max then "FFFF" else k[VALUE0010] ,Replacer.ReplaceValue,{"VALUE0010"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",each [VALUE0001],(k)=> let t=Table.SelectRows(#"Sorted Rows",each [XRef]=k[XRef] and [VALUE0001]<>""),
min = List.Min(t[TRef])-1,
max = List.Max(t[TRef])+1
in if k[TRef]=min or k[TRef]=max then "FFFF" else k[VALUE0001] ,Replacer.ReplaceValue,{"VALUE0001"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2",each [VALUE0011],(k)=> let t=Table.SelectRows(#"Sorted Rows",each [XRef]=k[XRef] and [VALUE0011]<>""),
min = List.Min(t[TRef])-1,
max = List.Max(t[TRef])+1
in if k[TRef]=min or k[TRef]=max then "FFFF" else k[VALUE0011] ,Replacer.ReplaceValue,{"VALUE0011"})
in
    #"Replaced Value3"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

[AlienSx · Accepted solution]

one more variation

let
    Source = your_table,
    // this function adds "FFFF" to arbitrary list of values
    ffff = (lst) => 
        [first = List.PositionOf(lst, "", Occurrence.First, (x, y) => x <> y),
        last = List.PositionOf(lst, "", Occurrence.Last, (x, y) => x <> y),
        replace_first = 
            if List.Contains({-1, 0}, first) 
            then lst 
            else List.ReplaceRange(lst, first - 1, 1, {"FFFF"}),
        replace_last = 
            if List.Contains({-1, List.Count(lst) - 1}, last) 
            then replace_first 
            else List.ReplaceRange(replace_first, last + 1, 1, {"FFFF"})][replace_last],

    // index col - just in case data change positions when Table.Group 
    idx = Table.AddIndexColumn(Source, "idx"),
    // group by XRef and apply ffff function to all columns but first 2
    group = Table.Group(
        idx, "XRef", 
        {"x", (x) => 
            [to_col = Table.ToColumns(Table.Sort(x, "idx")),
            result = Table.FromColumns(
                List.FirstN(to_col, 2) & 
                    List.Transform(List.RemoveFirstN(to_col, 2), ffff),
                Table.ColumnNames(x)
            )][result]}
    ),
    z = Table.Combine(group[x])
in
    z

[dufoq3 · Accepted solution]

Hi @roncruiser, different approach here:

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZZdbsMgEAbv4uc+AMbYPNYlXKLq/a/R7EcMGNjFUhtLzMiJ+Znk93fRy9ei3v/pL8a40+WMcfn7SlRX9GXp4t/SRU2m7lR0I+ety3TN1ChQRZeL2kzVgG4idSLdRXqI1ItUKxlrGRtMY6Bni1vwnEbz5reDPmjQhbeaxYs68LlwGWpb0WreajSlyl6Avdv+TDvSm7bv1mo+3W3y2Yy63Y17U6NvT8pq5pmGVbD6nGi0Ct75mUar4Pf3wRIXy9AqxKQFpyOnfXZ4iJFV5G1u5H2+yvt8ve1z02Ej47XGrsN1GBAk3CTjUgZ3Xo+/FlzSQMZ7Lgvaa1SGj/GwHw5b1Q3jAfPQJ6KZ3AL6yeueaR1QewsokSqg+r4KRPmAEuUDSpQPKFE+oET5nUWU31iYDX5jAes0g6Fchhq9hBjIDm4YUmjpCAOEcV5SC5ZZrKDRjKq55p5p+zPteKb5RxpC+kDTzzSzzOoNbU3a90RLIZ1qCKnbhN5Cq0LqnTk57QrpPgwpFHm7CyHFCZb3uxBSYD6kwHVItetwCYRpQgpcCvG6vkdMLLgJaaxQH1IM9yHFcB9S1KsPKTZKHqpDSkS3JP9OJWoq+kMP7rzbMy0T5fx9FYjwISXKh5QoH1KifEiJ8jsL4RKpEFLgFNL0U3McUmgI6Z4KuQ0PHTS8pCDo8dlMxpi32jbmrTYPKbR5SKExlWq19rtkrBmGt5p+pn1OthBSaGsaEQoJzT7TaBV+7CqEFJpbch616W6atSukbhhSKPJ2F0KKE3zb76bDt5C6DhsZj0JqCi6BsOs9pMClEIS+11IlJqJAfUQx3EcUw31EUa4mon//", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [XRef = _t, TRef = _t, VALUE0000 = _t, VALUE0010 = _t, VALUE0001 = _t, VALUE0011 = _t]),
    // You can probably delete this step
    ReplaceBlankToNull = Table.TransformColumns(Source, {}, each if _ = "" then null else _),
    ChangedType = Table.TransformColumnTypes(ReplaceBlankToNull,{{"XRef", Int64.Type}, {"TRef", Int64.Type}}),
    
    fn_ReplaceValue = 
        (myTable) as table =>
        let
            // T = GroupedRows{[XRef=2]}[All],
            T = myTable,
            ToCols = Table.ToColumns(T),
            ReplaceValue = List.Transform(ToCols, (x)=> 
                [ first = List.PositionOf(x, null, Occurrence.First, (y,z)=> y <> z),
                last = List.PositionOf(x, null, Occurrence.Last, (y,z)=> y <> z),
                b = List.Transform({0..List.Count(x)-1}, (y)=> if List.Contains({first -1, last +1}, y) then "FFFF" else x{y} )
                ][b]),
            FromCols = Table.FromColumns(ReplaceValue, Value.Type(T))
        in 
            FromCols,

    GroupedRows = Table.Group(ChangedType, {"XRef"}, {{"All", each fn_ReplaceValue(_), type table}}),
    CombinedAll = Table.Combine(GroupedRows[All])
in
    CombinedAll

[roncruiser]

@lbendlin  @AlienSx 

The solutions both work well with the dataset.  Thank you both!
I'm trying to decipher the M Code in both queries to learn more.

@AlienSx
The flexbility to have more columns use the function is very cool.  The column quanity does vary by project.  Thanks!

[AlienSx · Accepted solution]

one more variation

let
    Source = your_table,
    // this function adds "FFFF" to arbitrary list of values
    ffff = (lst) => 
        [first = List.PositionOf(lst, "", Occurrence.First, (x, y) => x <> y),
        last = List.PositionOf(lst, "", Occurrence.Last, (x, y) => x <> y),
        replace_first = 
            if List.Contains({-1, 0}, first) 
            then lst 
            else List.ReplaceRange(lst, first - 1, 1, {"FFFF"}),
        replace_last = 
            if List.Contains({-1, List.Count(lst) - 1}, last) 
            then replace_first 
            else List.ReplaceRange(replace_first, last + 1, 1, {"FFFF"})][replace_last],

    // index col - just in case data change positions when Table.Group 
    idx = Table.AddIndexColumn(Source, "idx"),
    // group by XRef and apply ffff function to all columns but first 2
    group = Table.Group(
        idx, "XRef", 
        {"x", (x) => 
            [to_col = Table.ToColumns(Table.Sort(x, "idx")),
            result = Table.FromColumns(
                List.FirstN(to_col, 2) & 
                    List.Transform(List.RemoveFirstN(to_col, 2), ffff),
                Table.ColumnNames(x)
            )][result]}
    ),
    z = Table.Combine(group[x])
in
    z

[roncruiser]

@AlienSx 

I have a few questions about your M code for my edification please:

//What does (x,y) represent?  I don't understand the concept behind it.  
//How did you know to use Occurence.First?  I could not find any documentation on it anywhere.

[first = List.PositionOf(lst, "", Occurrence.First, (x, y) => x <> y),

//I'm unsure what is happening here too. The offset of the position and the last and first position count is confusing me.  How do you get to the position count value of the last "" and the first "".  I tried mocking up an example for myself using an actual list like this
{"","","",1,2,3,4,5,6,"","",""} but I still don't understand yet.

       replace_first = 
            if List.Contains({-1, 0}, first) 
            then lst 
            else List.ReplaceRange(lst, first - 1, 1, {"FFFF"}),

Hope to get some clarity.  I may have more questions later.

Nonetheless, your code is pretty darn cool.

[AlienSx]

@roncruiser List.PositionOf : (x, y) => represents a comparison criteria mentioned in the documentation. We compare each item of the list with "value" argument. Occurrence.First/Last/All is

optional occurrence as nullable number

Check list functions documentation, parameter values section. 

Lets take a list 

{"","","",1,2,3,4,5,6,"","",""}

All positions of "" in the list: 

    List.PositionOf(
        {"","","",1,2,3,4,5,6,"","",""}, 
        "",
        Occurrence.All
    )

default equationCriteria is a function that returns true or false if values are equal or not. But we want to get a first of last position of item which is <> "". That's why we use custom 

optional equationCriteria as any

that returns true when list item is <> "". With combination of Occurrence argument we find first or last item that is <> "". 

[roncruiser]

@AlienSx 

I'm still not understanding something with the code below relative to your code.
I'm trying to replace the last value in the list "11" with "**".  I broke down your code and was able to replace the last value in the last row in the list with "**" using the code below.

let
    Source = {1,2,3,4,5,"",10,11},

        first = List.PositionOf(Source, "", Occurrence.First, (x, y) => x <> y),
        last = List.PositionOf(Source, "", Occurrence.Last, (x, y) => x <> y),
        last_ = List.Last(Source),
        replace_first = 
            if List.Contains({-1, 0}, first +1 ) 
            then Source 
            else List.ReplaceRange(Source, first  , 1, {"*"}), 
        replace_last = 
            if List.Contains({-1, List.Count(Source)  -1 }, last+1) 
            then replace_first
            else List.ReplaceRange(replace_first, last, 1, {"**"})
            in
            replace_last

My confusion, I am not able to do the same with the original code you wrote here:

    ffff = (lst) => 
        [first = List.PositionOf(lst, "", Occurrence.First, (x, y) => x <> y),
        last = List.PositionOf(lst, "", Occurrence.Last, (x, y) => x <> y),
        replace_first = 
            if List.Contains({-1, 0}, first) 
            then lst 
            else List.ReplaceRange(lst, first - 1, 1, {"*"}),
        replace_last = 
            if List.Contains({-1, List.Count(lst) - 1}, last) 
            then replace_first 
            else List.ReplaceRange(replace_first, last + 1, 1, {"**"})][replace_last],

 Other than the source list, the only difference is this line:
Yours
replace_last =
if List.Contains({-1, List.Count(lst) - 1}, last)  //If I add a +1 to your code, the group table shows errors.

Mine:
replace_last =
if List.Contains({-1, List.Count(Source) -1 }, last+1)  // I added a +1 and it works perfectly to replace the last value in the list.  In my break down code.

How can I get it to work in your code?  Replacing the last value in the last row in the list with "**" in your code for all columns.

So, would like to replace the last non-blank value at the bottom of the list at the very end of the list with "**" while maintaining the other original criteria from my original post.  Thanks again.

[AlienSx]

if List.Contains({-1, List.Count(lst) - 1}, last)

works as preventer. Last time you wanted to replace the very last occurrence of "". This piece of code does not allow to change anything if item <> "" is not found or the very last item <> "". Now you want to replace that last item if it is <> "". Try simple if last = -1 then <do_nothing> else List.ReplaceRange(...) with exact position of item instead. 

let
    ffff = (lst) => 
        [first = List.PositionOf(lst, "", Occurrence.First, (x, y) => x <> y),
        last = List.PositionOf(lst, "", Occurrence.Last, (x, y) => x <> y),
        replace_first = 
            if List.Contains({-1, 0}, first) 
            then lst 
            else List.ReplaceRange(lst, first - 1, 1, {"*"}),
        replace_last = 
            if last = -1 
            then replace_first 
            else List.ReplaceRange(replace_first, last, 1, {"**"})][replace_last],
    Source = {1,2,3,4,5,"",10,11},
    zz = ffff(Source)
in
    zz

[roncruiser]

@AlienSx 
Some context:
The solution I was striving for with my original post simply was not good enough.  Your code, help, and guidance helped realize my approach was not good enough.  Replacing the <>"" at the top and bottom of the list is the better solution.

By marking the top and bottom of the list, I can then strip away everything in between.  The biggest benefit to that is reducing load time.  Sometimes I/we simply don't need everything in between the top of the list and the bottom of the list.  Marking the top and bottom is good enough.

Our current solution loads everything in between which has it's benefits.  The biggest benefit is resolution.  The downside is that it takes a very very very long time to load all the data.

50% of the time the user does need to load everything in between.  They just want a simple health check so to speak.  Reducing load time at the expense of resolution is the "New" added benefit.  Based on rough testing, the load times reduced up to 80%.  Best case.  Which is significant.  Thanks to your inspiration!  I'm still realizing more possibilities with the help of your code.  It's like a door opened up to another realm.

I'd like to give the user incremental resolution control. 
Below on the left side is every basic scenario your code can cover.  Which covers everything the data can throw at us.  In general.

Is there a way to mark the top two and mark the bottom two within the list like the right side example?  There's a function to implement FirstN occurances and LastN occurances (top and bottom).  I've read about it somewhere over the last few days.

This will effectively double the resolution relative to the left side in the example below.

The trouble I see with this is the third example down on the right side.  How will doubling the markers be handled in yellow?   As long as a marker is indeed placed there will be good enough.

This is really cool stuff.   I know it's asking a lot, but I'm learning a lot too. 
Thank you again.

[roncruiser]

@AlienSx 
This is great.  Makes a bit more sense now.  I was trying to understand your code better yesterday by breaking it down and running piece by piece separately.

At a high level I understand what's going on, but the syntax and the way everything ties together is bit confusing.

I added some of my own comments.  Please ccomment if they look incorrect.
I have a question please:

//Question: Table.ColumnNames(x) grabs the column names.  How are the column names then 
//added back?
//After each column is converted to a list, the column names are lost.  Which part 
//of the syntax adds the column names back to the table?  

 group = Table.Group(
        idx, "XRef", 
        {"x", (x) =>  //"x" is the name of the column for the grouped tables 
            [to_col = Table.ToColumns(Table.Sort(x, "idx")), 
            //create list from each column in the table and sort each table by column "idx"

            result = Table.FromColumns(
                List.FirstN(to_col, 2) & 
                    List.Transform(List.RemoveFirstN(to_col, 2), ffff), 
                    //removes the first 2 columns and runs the ffff function on remaining.
                Table.ColumnNames(x)  
                //gets the columns names from the original source table.
            )][result]}
    ),
    z = Table.Combine(group[x])

Thanks @AlienSx !

[AlienSx]

@roncruiser column names are added back here: 

Table.FromColumns(lists as list, optional columns as any) as table

result = Table.FromColumns(...) 

[roncruiser]

@AlienSx 
Thanks for my knowledge support.
I learned how powerful lists are in the past 5 days.  Thanks to you.

[roncruiser]

@lbendlin 
I'm going to try it out first thing tomorrow!  Maybe when I get home this evening.
I will report back.  Thank You.

[lbendlin]

Why would there not be a FFFF in row 14 or 26?

[roncruiser]

Hi @lbendlin , ideal is no FFFF in 14 or 26, or anywhere between the top and bottom of the list of values per column.  If need be because I need a solution, there could be FFFF in 14 or 26, but it's not ideal to have it there.

FFFF should be before the first value in the list and FFFF should be after the last value in the list.  Per column.

Thank You.

[lbendlin]

Then why ist there one in row 28?  The column has values further down?

I don't understand your definition of "beginning" and "end". Can you please elaborate?

[roncruiser]

@lbendlin 

Sure.  Thanks.  Let me explain futher.  

Lets isolate and focus on XRef = 1 and the third column over [VALUE0000].
"beginning" = the first value in the column detected which is FFD5.  Place FFFF in previous row which is TRef = 10.
"end" = the last value in in the column detected which is F7FF.  Place FFFF in row after which is TRef = 27.

For columns like [VALUE001], TRef = 0 doesn't need FFFF added because TRef = 0 is the beginning and that's where the first value starts.  Though, FFFF is needed at the end Tref = 36 after value 6BFF.

The same FFFF add before the beginning value and after the end value for each value column will be done for each XRef.
In the power query code I added, the data only show three XRef's but there will likely be a few thousand XRef's in practice.

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZZLcqwwDEX3wrgHWDYGD9Mh3kSq97+NIDngnyT7VV53FecUYF9d6N/fxSyvxV3/0996/bu/Pq9EN5V6le4qPVQaVGpWHRsdw/UR44lri9sZJM3ifWwH3uhpMm81Rx/rQfdFX6y2Za3krYZburobiGfb57QjXbS9WquFdLbBvcFanU26KJhqpaIGcxql4Mx7oGEKwYeRhimEPUY9LMAUYtJOb6Kk/U/4GaOo6GMO+pxbec5x9+TqIpWri1SuLlK5ukjlNSGVl0SJ60ui6l7beeYvVgPa9tNQQGyFSUvDQ+DkBztN4bAmpG1V6UTNz2n7nHbMaWFKowpPaGZOg2X03CDNJu1roKUKDzWqsN+UppNWVDh4eEvaXeGdrTAp+rgrFUasVBi3Ra4wUrnCSOUKI5UrjFReE1VGpUqFCacKp9crX2HSqMJ76ubGxk0afaRRNPxUJIPnrbbxvNXGFSZtXGHShH60WvsU4zUQeKuZOQ34GrWaTUeUbpLm5jRM4dtZpcKk+eUppoHupI92V9izFSZFH/fBWziP+zXOeLH4vq70/Hgq6A+uP4ZLel7wD/VvOrMPzuf3+0OBua6tfsZCh0HHtsS+w+WTh1ZFJ3nwVtz4va824/zsQeN6zma0lygfPvjDgT3s1u4wcEnk91+Zwn9Ge35GF4txVQpQp2DqFEBPAfQUoE3B+A7nFKBJAeoUfu7fmBAzblKIBepTAD4F4FMAPgUrpmCbFOqm2CqFGL8dpeD3h+aN8qGvaP0ahQ5XIfkOg465kCDjHJKzdUi2DgnRl80rFgKyfECWD8jyAVkmoM8f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [XRef = _t, TRef = _t, VALUE0000 = _t, VALUE0010 = _t, VALUE0001 = _t, VALUE0011 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"XRef", Int64.Type}, {"TRef", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"XRef", Order.Ascending}, {"TRef", Order.Ascending}}),
    #"Replaced Value" = Table.ReplaceValue(#"Sorted Rows",each [VALUE0000],(k)=> let t=Table.SelectRows(#"Sorted Rows",each [XRef]=k[XRef] and [VALUE0000]<>""),
min = List.Min(t[TRef])-1,
max = List.Max(t[TRef])+1
in if k[TRef]=min or k[TRef]=max then "FFFF" else k[VALUE0000] ,Replacer.ReplaceValue,{"VALUE0000"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each [VALUE0010],(k)=> let t=Table.SelectRows(#"Sorted Rows",each [XRef]=k[XRef] and [VALUE0010]<>""),
min = List.Min(t[TRef])-1,
max = List.Max(t[TRef])+1
in if k[TRef]=min or k[TRef]=max then "FFFF" else k[VALUE0010] ,Replacer.ReplaceValue,{"VALUE0010"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",each [VALUE0001],(k)=> let t=Table.SelectRows(#"Sorted Rows",each [XRef]=k[XRef] and [VALUE0001]<>""),
min = List.Min(t[TRef])-1,
max = List.Max(t[TRef])+1
in if k[TRef]=min or k[TRef]=max then "FFFF" else k[VALUE0001] ,Replacer.ReplaceValue,{"VALUE0001"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2",each [VALUE0011],(k)=> let t=Table.SelectRows(#"Sorted Rows",each [XRef]=k[XRef] and [VALUE0011]<>""),
min = List.Min(t[TRef])-1,
max = List.Max(t[TRef])+1
in if k[TRef]=min or k[TRef]=max then "FFFF" else k[VALUE0011] ,Replacer.ReplaceValue,{"VALUE0011"})
in
    #"Replaced Value3"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.