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]

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

[Syndicate_Admin]

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.

[Syndicate_Admin]

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?

[Syndicate_Admin]

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

[Syndicate_Admin]

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.

[Syndicate_Admin]

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

[Syndicate_Admin]

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

[Syndicate_Admin]

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

[Syndicate_Admin]

@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 is confusing me.

       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.