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

Thanks Lorenzo 😊

I guess the most helpful thing for me to do is describe a common structure for my workbooks. I aim to build templates, so that I am not always reinventing the wheel each time that statistics are required. 

• First tab is a quick ReadMe/UserGuide for customers
• Second tab is the raw data, formatted as an Excel table
• The contents of next tab varies depending on the audience, and purpose of the file. If I load the transformed data for the customers to play with directly, this is where it resides.
• The next tab would usually contain dynamic, interactive statistics and charts - a dashboard if you will
• There may be a final hidden sheet, with helper tables etc, if I've included/required them

For my work, I have found that using a table to store my raw data has been more flexible or maybe more robust than using a range - and definitely more robust than linking to an external data source, as the files get sent everywhere. I've learned through experience that it's best for me to contain the raw data with the transformations and the output :)

The template contains a table with on the raw data tab with the header row and an empty first data row. The complete range of raw data is copied from the CSV and pasted as Values Only into the table - meaning that formatting is retained and that the table expands in size to fit. I hope that this answers your question 🙂

Trying to keep my skills contemporary, I hopped into some GenAI (tried Copilot, then Gemini Advanced, and then back to Copilot) for assistance with those last two things that I wanted to solve (convert blank to null and check Data Type) and am satisfied with the outcome. The amended QueryResult code is below and I've attached the file in case you are curious about what AI chose to do:

let
    Source = JiraExport,

    SourceHeaders = Table.FromColumns( { Table.ColumnNames( Source ) }, {"JiraHeader"} ),
    SelectedAssumedHeaders = Table.SelectRows( SourceHeaders, each
        Text.StartsWith( [JiraHeader], Labels_StartsWith ) or
        Text.StartsWith( [JiraHeader], CustomFields_StartsWith )
    ),
    HeaderStartNames = Table.AddColumn(SelectedAssumedHeaders, "JiraHeaderStartName", each
        if Text.StartsWith( [JiraHeader], Labels_StartsWith ) then Labels_StartsWith
        else Text.BeforeDelimiter( [JiraHeader], ")", {0, RelativePosition.FromEnd} )),
    GroupedStartNames = Table.Group( HeaderStartNames,
        {"JiraHeaderStartName"},
            {
                {"DATA", each _, type table},
                {"Count", each Table.RowCount(_), Int64.Type}
            }
    ),
    FilteredCount = Table.SelectRows( GroupedStartNames, each ( [Count] > 1 ) ),
    List_HeadersToMerge = Table.ExpandTableColumn(
        Table.SelectColumns( FilteredCount, {"DATA"} ),
        "DATA", {"JiraHeader"}, {"JiraHeaders"}
    )[JiraHeaders],
    List_HeadersStartsWith = FilteredCount[JiraHeaderStartName],
    MergedColumns = List.Accumulate( List_HeadersStartsWith, Source,
        (accum,name) as table =>
            Table.CombineColumns(
                accum,
                List.Select( List_HeadersToMerge, each Text.StartsWith(_, name ) ),
                each Combiner.CombineTextByDelimiter(" ; ", QuoteStyle.None)(List.RemoveNulls(_) ),
                name
            )
    ),
    ColumnsToRename = List.Select(
        Table.ColumnNames( MergedColumns ), each
        Text.StartsWith(_, CustomFields_StartsWith )
    ),
    NewColumnNames = List.Transform( ColumnsToRename, each
        let
            BetweenDelimiters = Text.BetweenDelimiters(_, CustomFields_StartsWith, ")" )
        in
            if Text.Contains( BetweenDelimiters, "(" ) then BetweenDelimiters & ")" else BetweenDelimiters
    ),
    RenamedMergedColumns = Table.RenameColumns( MergedColumns,
        List.Zip( { ColumnsToRename, NewColumnNames} )
    ),

    Result = if Table.IsEmpty( FilteredCount ) then Source else RenamedMergedColumns,
    EmptyToNull = Table.TransformColumns(
        Result,
        List.Transform(
            Table.ColumnNames(Result),
            (colName) => {colName, each if _ = "" then null else _}
        )
    ),

    // Function to detect data type
    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 number // Check for whole 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

    // Apply the function to each column
    ColumnTypes = List.Transform(
        Table.ColumnNames(EmptyToNull),
        (colName) => 
            let
                columnValues = Table.Column(EmptyToNull, colName) // Get the entire column values
            in
                {colName, DetectDataType(columnValues)} // Pass the column values to the function
    ),
    
    // Transform the table with detected types
    DetectDataTypes = Table.TransformColumnTypes(EmptyToNull, ColumnTypes),
    #"Sorted Rows" = Table.Sort(DetectDataTypes,{{"Issue key", Order.Ascending}})
in
    #"Sorted Rows"