i have three solutions to this (starting with a column containing Records):

1. custom function to expand the record column and from the new table, expand all underlying record columns and lists

   1. with dynamic rename of columns of new tables if the column-name already exists

2. custom function to expand only the record column

3. custom function to expand all records and all underyling records and lists RECURSIVELY

   1. takes ages

   2. with dynamic rename of columns of new tables if the column-name already exists -> FAILS SOMETIMES xD

[RECOMMENDATION]: Go for option 1. Why? -> Overall least effort and Power Query does it fastest.

1. look for the record columns in your table and paste them into the function like this by nesting muiltiple functions:
   =expandRecordColAndFirstLevel(expandRecordColAndFirstLevel(#"Previous step","components"),"hotel")

2. ENTER

3. in the new table, search for more record columns and nest them with functions around your original query:
   =expandRecordColAndFirstLevel(expandRecordColAndFirstLevel(expandRecordColAndFirstLevel(expandRecordColAndFirstLevel(expandRecordColAndFirstLevel(expandRecordColAndFirstLevel(#"Expanded components1","components"),"hotel"),"payment_term"),"location"),"stages"),"address")

4. and so on...

5. In terms of query performance, it's better to nest the queries into 1 step even as well. This query took 45 seconds. Doing the expandRecordColAndFirstLevel(...) function in seperate steps takes 1,5x-2x of time.

here's the m-code for the custom functions (paste in the advanced editor of your function):

1 expandRecordColAndFirstLevel

(inputTable as table, columnName as text) as table =>
let
    NonNullValues = List.Select(Table.Column(inputTable, columnName), each _ <> null and _ <> ""),
    SampleValue = try NonNullValues{0} otherwise null,

    ExpandedStep =
        if SampleValue is record then
            let
                // Step 1: Get inner fields
                ColumnsToExpand = try Table.ColumnNames(Table.FromRecords(NonNullValues)) otherwise {},
                ExistingCols = Table.ColumnNames(inputTable),
                RenamedCols = List.Transform(ColumnsToExpand, (c) =>
                    if List.Contains(ExistingCols, c) then c & "." & columnName else c
                ),
                Step1 = Table.ExpandRecordColumn(inputTable, columnName, ColumnsToExpand, RenamedCols),

                // Step 2: Check for list fields among newly added columns
                NewListColumns = List.Select(RenamedCols, (col) =>
                    let sample = try Table.Column(Step1, col){0} otherwise null
                    in sample is list
                ),

                // Step 3: Expand those list fields
                Final = List.Accumulate(NewListColumns, Step1, (state, col) => 
                    Table.ExpandListColumn(state, col)
                )
            in
                Final

        else if SampleValue is list then
            Table.ExpandListColumn(inputTable, columnName)

        else
            inputTable
in
    ExpandedStep

2 expandRecordCol

(inputTable as table, columnName as text) as table =>
let
    NonNullRecords = List.Select(
        Table.Column(inputTable, columnName),
        each _ <> null and _ <> ""
    ),
    ColumnsToExpand = Table.ColumnNames(Table.FromRecords(NonNullRecords)),
    ExistingColumns = Table.ColumnNames(inputTable),

    RenamingMap = List.Transform(
        ColumnsToExpand,
        (col) =>
            if List.Contains(ExistingColumns, col)
            then {col, col & "." & columnName}
            else {col, col}
    ),

    Expanded = Table.ExpandRecordColumn(inputTable, columnName, ColumnsToExpand, List.Transform(RenamingMap, each _{1}))
in
    Expanded

3 expandRecordColRecursive

let
    RecursiveExpandColumn = (inputTable as table, columnName as text, optional path as text) as table =>
    let
        // Defaults
        currentPath = if path = null then columnName else path & "." & columnName,

        // Record Expansion with Safe Naming
        ExpandRecordColumn = (tbl as table, colName as text, fullPath as text) as table =>
            let
                nonNulls = List.Select(Table.Column(tbl, colName), each _ <> null and _ <> ""),
                colsToExpand = try Table.ColumnNames(Table.FromRecords(nonNulls)) otherwise {},
                existingCols = Table.ColumnNames(tbl),
                newNames = List.Transform(colsToExpand, (c) =>
                    if List.Contains(existingCols, c) or List.Contains(existingCols, fullPath & "." & c) then
                        c & "." & fullPath
                    else
                        c
                ),
                expanded = Table.ExpandRecordColumn(tbl, colName, colsToExpand, newNames)
            in
                expanded,

        // List Expansion
        ExpandListColumn = (tbl as table, colName as text) as table =>
            Table.ExpandListColumn(tbl, colName),

        // Step 1: Expand the root column based on its type
        sample = try Table.Column(inputTable, columnName){0} otherwise null,
        firstExpanded =
            if sample is record then ExpandRecordColumn(inputTable, columnName, currentPath)
            else if sample is list then ExpandListColumn(inputTable, columnName)
            else inputTable,

        // Step 2: Recursively process any new complex columns
        FullyExpand = (tbl as table) as table =>
            let
                // Get columns that are still records/lists
                complexCols = List.Select(Table.ColumnNames(tbl), (col) =>
                    let v = try Table.Column(tbl, col){0} otherwise null
                    in v is record or v is list
                ),
                // Skip if no complex columns left
                nextStep =
                    if List.IsEmpty(complexCols) then
                        tbl
                    else
                        List.Accumulate(complexCols, tbl, (state, col) =>
                            let v = try Table.Column(state, col){0} otherwise null in
                            if v is record then
                                @RecursiveExpandColumn(state, col, col)
                            else if v is list then
                                @RecursiveExpandColumn(state, col, col)
                            else
                                state
                        )
            in
                nextStep

    in
        FullyExpand(firstExpanded)
in
    RecursiveExpandColumn