let
    listReplaceText =
        List.Buffer(
            {
                "ул.", "г.", "с.", "пр-кт.", "пом.", "зд.", "им.", "улица",
                "п.", "пр-кт", "пгт.", "рп.", "пер.", "стр.", "пр-т",
                "пр.", "№", "пл.", "б-р", "аул", "корпус", "ст-ца.", ",", ".", "-"
            }
        ),
    listCount = List.Count( listReplaceText ),
    fTextReplace = ( srcText, step ) => 
        if step < listCount
        then @fTextReplace( Text.Replace( srcText, listReplaceText{ step }, "" ), step + 1 )
        else srcText,

    fTable = ( tableName, columnName ) => [
        excelCurrentWorkbook = Excel.CurrentWorkbook( ){ [ Name = tableName ] }[ Content ],
        tableSelectRows = Table.SelectRows( excelCurrentWorkbook, ( x ) => Record.Field( x, columnName ) <> null ),
        tableDistinct = Table.Distinct( tableSelectRows ),
        tableAddColumn =
            Table.AddColumn(
                tableDistinct,
                columnName & "_Fix",
                ( x ) => [
                    recordField = Record.Field( x, columnName ),
                    textFrom = Text.From( recordField ),
                    textReplace = fTextReplace( textFrom , 0 )
                ][ textReplace ],
                type text
            )        
    ][ tableAddColumn ],

    tableLeft = fTable( "Table1", "Адрес 1" ),
    tableRight = fTable( "Table2", "Адрес 2" ),
    tableAddIndexColumn = Table.AddIndexColumn( tableLeft, "Index" ),

    tableFuzzyJoin =
        Table.FuzzyJoin(
            tableAddIndexColumn, { "Адрес 1_Fix" },
            tableRight, { "Адрес 2_Fix" },
            JoinKind.LeftOuter,
            [
                IgnoreCase = true,
                IgnoreSpace = true,
                Threshold = 0.8
            ]
        ),
    tableGroup =
        Table.Group(
            tableFuzzyJoin,
            { "Адрес 1" },
            {
                {
                    "Адрес 2",
                    ( x ) => Text.Combine( x[Адрес 2], "#(lf)" ),
                    type text
                },
                {
                    "Количество адресов похожих",
                    ( x ) => List.Count( List.RemoveNulls( x[Адрес 2] ) ),
                    Int64.Type
                }
            }
        )
in
    tableGroup

let
    Source = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
    tableSelectRows = Table.SelectRows( Source, ( x ) => x[ Столбец1 ] <> null ),
    tableToList =
        Table.ToList(
            tableSelectRows,
            ( x ) => [
                textSplit = Text.Split( Text.From( x{ 0 } ), "#(lf)" ),
                listCombine = { textSplit } & { x{ 1 } }
            ][ listCombine ]
        ),
    tableFromList = Table.FromList( tableToList, ( x ) => x ),
    tableExpandListColumn = Table.ExpandListColumn( tableFromList, "Column1" )
in
    tableExpandListColumn

let
    a = List.PositionOf({1..10},5), //4
    b = List.PositionOf({"a".."z"},"d"), //3 
    c = List.PositionOf({1..10},11), //-1 - значение не найдено
    d = List.PositionOf({1,2,null,3,4,null,5},null), //2
    e = List.PositionOf({1,2,null,3,4,null,5},null,Occurrence.First),//2
    f = List.PositionOf({1,2,null,3,4,null,5},null,Occurrence.Last), //5
    g = List.PositionOf({1,2,null,3,4,null,5},null,Occurrence.All), //{2,5}
    h = List.PositionOf({1,2,null,3,4,null,5},null,Occurrence.First,(c,v)=>c<>v),//0
    i = List.PositionOf({1,2,null,3,4,null,5},null,Occurrence.Last,(c,v)=>c<>v),//6
    j = List.PositionOf({1,2,null,3,4,null,5},null,Occurrence.All,(c,v)=>c<>v),//{0,1,3,4,6}
    k = List.PositionOf({1..5},3,Occurrence.All,(c,v)=>c<v), //{0,1}
    l = List.PositionOf({1..5},3,Occurrence.All,(c,v)=>c>v), //{3,4}
    m = List.PositionOf({1,2,null,3,4,null,5},3,Occurrence.All,(c,v)=>if c is null then false else c>v),//{4,6}
    n = List.PositionOf({"a".."z"},"D",Occurrence.All,Comparer.OrdinalIgnoreCase) //3
in
    n

Text.RemoveRange( [ текст ], 0 )

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tableAddColumn =
        Table.AddColumn(
            Source,
            "Количество",
            ( x ) =>
                if x[ Тип ] = "покупка"
                then x[ #"Кол-во" ]
                else -x[ #"Кол-во" ]
        ),
    tableGroup =
        Table.Group(
            tableAddColumn,
            { "№ карты", "Фамилия" },
            {
                {
                    "Количество",
                    ( x ) => List.Sum( x[ Количество ] ),
                    type number
                }
            }
        )
in
    tableGroup

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tableToList =
        Table.ToList(
            Source,
            ( x ) => [
                textSplit = Text.Split( x{ 0 }, "/" ),
                listReplaceValue = List.ReplaceValue( textSplit, textSplit{ 4 }, x{ 1 }, Replacer.ReplaceText ),
                textCombine = Text.Combine( listReplaceValue, "/" )
            ][ textCombine ]
        )
in
    tableToList

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tableToList =
        Table.ToList(
            Source,
            ( x ) => [
                textSplit = Text.Split( x{ 0 }, "/" ),
                listReplaceRange = List.ReplaceRange( textSplit, 4, 1, { x{ 1 } } ),
                textCombine = Text.Combine( listReplaceRange, "/" )
            ][ textCombine ]
        )
in
    tableToList

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tableGroup =
        Table.Group(
            Source,
            { "Description" },
            {
                {
                    "temp",
                    ( x ) => [
                        listSum = List.Sum( x[ Amount ] ),
                        Account = x[ Description ]{ 0 },
                        tableAddColumn =
                            Table.AddColumn(
                                x,
                                "SumColumn",
                                ( y ) => if Account = y[ Account ] then listSum else null
                            )
                    ][ tableAddColumn ],
                    type table
                }
            }
        ),
    tableCombine = Table.Combine( tableGroup[ temp ] )
in
    tableCombine

let
    codes = Excel.CurrentWorkbook(){[Name="codes"]}[Content],
    tableRemoveColumns = Table.SelectColumns(codes,{"Номер", "Регион"}),
    tableRenameColumns = Table.RenameColumns(tableRemoveColumns,{{"Номер", "Name"}, {"Регион", "Value"}}),
    tableTransformColumnTypes = Table.TransformColumnTypes(tableRenameColumns,{{"Name", type text}}),
    recordFromTable = Record.FromTable( tableTransformColumnTypes ),
    listTransform = List.Buffer( List.Transform( tableTransformColumnTypes[ Name ], Text.Length ) ),
    maxLength = List.Max( listTransform ),
    minLength = List.Min( listTransform ),

    t = Excel.CurrentWorkbook(){[Name="tel"]}[Content],
    tableAddColumn =
        Table.AddColumn(
            t,
            "Регион",
            ( x ) => [
                textFrom = Text.From( x[ Телефон ] ),
                textEnd = Text.End( textFrom, 10 ),
                textStart = Text.Start( textEnd, maxLength ),

                rec = ( textSrc, textLength ) => [
                    textLook = Text.Start( textSrc, textLength  ),
                    result = Record.FieldOrDefault( recordFromTable, textLook ),
                    circle = if result <> null or textLength = minLength then result else @rec( textLook, textLength - 1 )
                ][ circle ],

                result = rec( textStart, maxLength )
            ][ result ]
        )
in
    tableAddColumn

let
    codes = Excel.CurrentWorkbook(){[Name="codes"]}[Content],
    tableRemoveColumns = Table.SelectColumns(codes,{"Номер", "Регион"}),
    tableRenameColumns = Table.RenameColumns(tableRemoveColumns,{{"Номер", "Name"}, {"Регион", "Value"}}),
    tableTransformColumnTypes = Table.TransformColumnTypes(tableRenameColumns,{{"Name", type text}}),
    recordFromTable = Record.FromTable( tableTransformColumnTypes ),
    listTransform = List.Buffer( List.Transform( tableTransformColumnTypes[ Name ], Text.Length ) ),
    maxLength = List.Max( listTransform ),
    minLength = List.Min( listTransform ) - 1,

    t = Excel.CurrentWorkbook(){[Name="tel"]}[Content],
    tableTransformColumnTypes_Text = Table.TransformColumnTypes(t,{{"Телефон", type text}}),
    tableAddColumn =
        Table.AddColumn(
            tableTransformColumnTypes_Text,
            "Регион",
            ( x ) => [
                textEnd = Text.End( x[ Телефон ], 10 ),
                textStart = Text.Start( textEnd, maxLength ),

                rec = ( textSrc, textLength ) => [
                    field = Record.FieldOrDefault( recordFromTable, textSrc ),
                    circle =
                        if field <> null or textLength = minLength
                        then field
                        else @rec( Text.Start( textSrc, textLength ), textLength - 1 )
                ][ circle ],

                result = rec( textStart, maxLength - 1 )
            ][ result ]
        )
in
    tableAddColumn

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tableTransformColumns =
        Table.TransformColumns(
            Source,
            {
                "Атрибут",
                ( x ) => Text.BeforeDelimiter( x, "." )
            }
        ),
    tableGroup =
        Table.Group(
            tableTransformColumns,
            { "Код ТМЦ", "Заказ" },
            {
                {
                    "temp",
                    ( x ) => [
                        tableGroup_Атрибут =
                            Table.Group(
                                x,
                                { "Атрибут" },
                                {
                                    { "temp", ( z ) => z[ Значение ] }
                                }
                            ),
                            tableFromColumns_Атрибут =
                                Table.FromColumns(
                                    tableGroup_Атрибут[ temp ],
                                    tableGroup_Атрибут[ Атрибут ]
                                )
                    ][ tableFromColumns_Атрибут ],
                    type table
                }
            }
        ),
    #"Expanded {0}" = Table.ExpandTableColumn(tableGroup, "temp", {"Технаряды", "Создатели ТН", "Даты создания ТН", "Кол-во по ТН"}, {"Технаряды", "Создатели ТН", "Даты создания ТН", "Кол-во по ТН"})
in
    #"Expanded {0}"

let
    src_1 = [
        excelCurrentWorkbook = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
        tableRenameColumns =
            Table.RenameColumns(
                excelCurrentWorkbook,
                {
                    { "Номер телефона", "number" },
                    { "Дата и время обращения", "Дата и время первого обращения" }
                }
            )
    ][ tableRenameColumns ],

    src_2 = Excel.CurrentWorkbook(){[Name="Таблица13"]}[Content],
    tableJoin =
        Table.Join(
            src_1, { "number" },
            src_2, { "Номер телефона" }
        ),
    tableGroup =
        Table.Group(
            tableJoin,
            { "number", "Дата и время первого обращения" },
            {
                {
                    "temp",
                    ( x ) => [
                        dateTime_First = x[ #"Дата и время первого обращения" ]{ 0 },
                        tableSelectRows =
                            Table.SelectRows(
                                x,
                                ( y ) => [
                                    duration = Duration.TotalMinutes( y[ #"Дата и время обращения" ] - dateTime_First ),
                                    cond = duration > 0 and duration <= 20
                                ][ cond ]
                            ),
                        tableSelectColumns = Table.SelectColumns( tableSelectRows, { "Номер телефона", "Дата и время первого обращения", "Дата и время обращения" } )
                    ][ tableSelectColumns ],
                    type table
                }
            }
        ),
    tableCombine = Table.Combine( tableGroup[ temp ] )
in
    tableCombine

let
    Source = Excel.CurrentWorkbook(){[Name="СкладУчет"]}[Content],
    tableGroup_step1 =
        Table.Group(
            Source,
            { "Артикул" },
            {
                {
                    "temp",
                    ( x ) => [
                        sValue = x[ #"Номер стелажа" ]{ 0 },
                        tableAddColumn = Table.AddColumn( x, "key_step1", ( y ) => sValue, type text )
                    ][ tableAddColumn ],
                    type table
                }
            }
        ),
    tableCombine_step1 = Table.Combine( tableGroup_step1[ temp ] ),
    tableGroup_step2 =
        Table.Group(
            tableCombine_step1,
            { "Номер стелажа" },
            {
                {
                    "temp",
                    ( x ) => [
                        sValue = List.Sort( x[ key_step1 ] ){ 0 },
                        tableAddColumn = Table.AddColumn( x, "key_step2", ( y ) => sValue, type text )
                    ][ tableAddColumn ],
                    type table
                }
            }
        ),
    tableCombine_step2 = Table.Combine( tableGroup_step2[ temp ] ),
    tableGroup_step2_1 =
        Table.Group(
            tableCombine_step2,
            { "Артикул" },
            {
                {
                    "temp",
                    ( x ) => [
                        sValue = x[ key_step2 ]{ 0 },
                        tableAddColumn = Table.AddColumn( x, "key_step2_1", ( y ) => sValue, type text )
                    ][ tableAddColumn ],
                    type table
                }
            }
        ),
    tableCombine_step2_1 = Table.Combine( tableGroup_step2_1[ temp ] ),
    tableGroup_step3 =
        Table.Group(
            tableCombine_step2_1,
            { "key_step2" },
            {
                {
                    "temp",
                    ( x ) => [
                        sValue = x[ key_step2_1 ]{ 0 },                     
                        tableAddColumn = Table.AddColumn( x, "key", ( y ) => sValue, type text )
                    ][ tableAddColumn ],
                    type table
                }
            }
        ),
    tableCombine_step3 = Table.Combine( tableGroup_step3[ temp ] ),
    tableColumnNames = List.Distinct( tableCombine_step3[ Брэнд ] ),
    tableGroup =
        Table.Group(
            tableCombine_step3,
            { "key" },
            {
                {
                    "temp",
                    ( x ) => [
                        src = x,
                        tableDistinct = Table.Distinct( x, { "key", "Артикул", "Брэнд" } ),
                        tableGroup =
                            Table.Group(
                                tableDistinct,
                                { "Брэнд" },
                                {
                                    {
                                        "temp",
                                        ( y ) => y[ Артикул ]
                                    }
                                }
                            ),
                        tableRowCount = Table.RowCount( tableGroup ),

                        Брэнд = List.Buffer( tableGroup[ Брэнд ] ),
                        Артикул = List.Buffer( tableGroup[ temp ] ),

                        fMakeTable = ( leftTable, rightList, rightName ) =>
                            Table.Join(
                                leftTable, {},
                                Table.FromColumns( { rightList }, { rightName } ), {}
                            ),
                        tableFromColumns_First = Table.FromColumns( { Артикул{ 0 } }, { Брэнд{ 0 } } ),
                        listGenerate =
                            if tableRowCount = 1 then tableFromColumns_First
                            else if tableRowCount = 2
                                then
                                    fMakeTable(
                                        tableFromColumns_First,
                                        Артикул{ 1 }, Брэнд{ 1 }
                                    )
                            else
                                List.Last(
                                    List.Generate(
                                        ( ) => [
                                            i = 1,
                                            tableJoin =
                                                fMakeTable(
                                                    tableFromColumns_First,
                                                    Артикул{ 1 }, Брэнд{ 1 }
                                                )
                                        ],
                                        each [ i ] < tableRowCount,
                                        each [
                                            i = [ i ] + 1,
                                            tableJoin = fMakeTable( [ tableJoin ], Артикул{ i }, Брэнд{ i } )
                                        ],
                                        each [ tableJoin ] 
                                    )
                                )
                    ][ listGenerate ],
                    type table
                }
            }
        ),
    tableExpandTableColumn = Table.ExpandTableColumn(tableGroup, "temp", tableColumnNames, tableColumnNames)
in
    tableExpandTableColumn

lst = List.Buffer(искомое[Столбец2]),
found = Table.AddColumn(full_descr, "позиция", (x) => List.Select(lst, (w) => Text.Contains(x[description], w)){0}?)

let
    listValue = { "test", "test2", "test3" },
    textCombine = "'" & Text.Combine( listValue, "', '" ) & "'"
in
    textCombine

let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],

    tableTransformRows =
        Table.TransformRows(
            Source,
            ( x ) =>
                Record.TransformFields(
                    x,
                    {
                        "Column2",
                        ( y ) =>
                            if y = null then x[ Column1 ]
                            else if x[ Column1 ] = "Текст4" then Text.Combine( { x[ Column1 ], y }, "||" )
                            else y
                    }
                )
        ),
    tableFromRecords = Table.FromRecords( tableTransformRows )
in
    tableFromRecords

let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],

    tableToRecords = Table.ToRecords( Source ),
    listTransform =
        List.Transform(
            tableToRecords,
            ( x ) =>
                Record.TransformFields(
                    x,
                    {
                        "Column2",
                        ( y ) => 
                            if y = null then x[ Column1 ]
                            else if x[ Column1 ] = "Текст4" then Text.Combine( { x[ Column1 ], y }, "||" )
                            else y
                    }
                )
        ),
    tableFromRecords = Table.FromRecords( listTransform )
in
    tableFromRecords

tableColumnNames = List.Skip( Table.ColumnNames( ЦеныТО ) ),
#"Развернутый элемент ЦеныТО" = Table.ExpandTableColumn(#"Объединенные запросы", "ЦеныТО", tableColumnNames, tableColumnNames)

PrevStep = #"Expanded {0}2", // !!! Вставить название предыдущего шага !!!
    columnsExpandName = "Column1.properties.CompanyMetaData.Categories", // расширить столбец

    tableTransformColumns =
        Table.TransformColumns(
            PrevStep,
            {
                columnsExpandName,
                ( x ) => [
                    NameCat =
                        List.Transform(
                            x,
                            ( y ) => [
                                recordToList = Record.ToList( y ),
                                listCount = List.Count( recordToList ),
                                name = { if listCount > 1 then recordToList{ 1 } else recordToList{ 0 } }
                            ][ name ]
                        ),
                    listPositions = List.Positions( NameCat ),
                    NewNameColumns =
                        List.Transform(
                            listPositions,
                            ( y ) => columnsExpandName & Text.From( y ) 
                        ),
                    tableFromColumns = Table.FromColumns( NameCat, NewNameColumns )
                ][ tableFromColumns ]
            }
        ),
    
    expandTableColumnsName = [
        maxColumns = [
            tableRenameColumns = Table.RenameColumns( PrevStep, { columnsExpandName, "1" } )[ 1 ],
            listTransform = List.Transform( tableRenameColumns, List.Count ),
            listMax = List.Max( listTransform )
        ][ listMax ],
        listPositions = { "0" .. Text.From( maxColumns ) },
        listTransform = List.Transform( listPositions, ( x ) => columnsExpandName & x )
    ][ listTransform ],

    tableExpandTableColumn = Table.ExpandTableColumn( tableTransformColumns, columnsExpandName, expandTableColumnsName, expandTableColumnsName )

"{""code"";""" & Код & """}"