Power Query - removing identical generic text from the start and end of multiple columns

Hi Insert_Key 

Thanks for sharing all what you shared and TBH I wouldn't have bet on GenAI regarding data types. What they came up with is very similar to what I've seen so far and what I referred to in my previous reply. Though, AI got it wrong when it comes to numbers vs. whole numbers... In the workbook you shared - just for testing purpose - enter a decimal number in column [Parent id] then refresh the query... 😒

In attached v1.2 I fixed it as follow:

DetectDataType = (column as list) as type =>
if List.IsEmpty(column) then type nullable text // Default to text if empty
else 
    let
        sampleValue = List.First(List.RemoveNulls(column)), // Get the first non-null value
        valueType = Value.Type(sampleValue)
    in
        if valueType = type text then type nullable text
        else if valueType = type number and (sampleValue = Number.Round(sampleValue)) then type nullable Int64.Type // Check for whole numbers
        else if valueType = type number then type nullable number   // Decimal numbers
        else if valueType = type logical then type nullable logical
        else if valueType = type date or valueType = type datetime then type nullable datetime // Handle Date/Time
        else type nullable text, // Fallback to text

and IMHO the last else should be type nullable any and not text but that doesn't matter in your context as any will end as text once the query loads to the Data Model

A comment, just in case... I see you added a Sort step at the end of your query. I guess that's for checks on the query output within Excel. Otherwise, as far as I know, the Data Model doesn't mind about ordered data

Last thing. Now that I understand your solution has no external connection, you can slightly speed up the time to refresh your query by disabling the PQ Firewall. Excel data (tab) > Get Data > Query Options... > CURRENT WORKBOOK > Privacy > Choose Ignore the Privacy Levels...

Hope this helps and makes sense