🚀 Power Query Tip: Consecutive Completed Records in ONE Step:

When you want the latest consecutive “Completed” rows from a table, the obvious approach feels like a two-step process:

• Sort by Date (oldest → newest).

• Pick the consecutive rows where Status = “Completed”.

But Power Query’s Table.MaxN collapses this into a single, elegant step:

let

 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

 Result = Table.MaxN(Source, "Date", each [Status] = "Completed")

in

 Result

✨ This returns the most recent Completed records directly — no manual sorting needed.

🌟 Power Query Tip: Utilizing ExtraValues.List

When splitting text in Power Query, you’ll often run into rows that don’t have the same number of parts.

If you use Table.SplitColumn with fixed column names, Power Query doesn’t know what to do with the “extra” values. That’s where ExtraValues.List comes in.

let

 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

 Result = Table.SplitColumn(Source,"Data",each Text.Split(_,"|"),{"Date","Product","Sales"},null,ExtraValues.List)

in

 Result

• Instead of discarding or misaligning extra values, Power Query collects them into a list column.

• You get a clean Date and Product column plus a Sales column that contains a list of all the extra values for that row.

👉 The key takeaway:

ExtraValues.List is your safety net for irregular data. It ensures nothing gets lost, and you stay in control of how to reshape it later.