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

[Syndicate_Admin]

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.

Download_pbix 

[Syndicate_Admin]

@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 <> "". 

[Syndicate_Admin]

@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])

[Syndicate_Admin]

@roncruiser column names are added back here: 

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

result = Table.FromColumns(...) 

[Syndicate_Admin]

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

[Syndicate_Admin]

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

[Syndicate_Admin]

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

[Syndicate_Admin]

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),
            FirstNoNullPosition = 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(FirstNoNullPosition, Value.Type(T))
        in 
            FromCols,

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

[Syndicate_Admin]

@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 use.  In general.

Is there a way to mark the top two and mark the bottom two within the list like the right side example?  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.