Extract Common Text

[MrDB4Excel]

I have a folder in which I retain downloaded teaching aids that are either xlsm or xlsx; and YouTube & website links as well as various documents related to the reason for the folder name.
Following is the folder name including the sub-folder names.
Main folder: SpecialExcelSheets
Sub-folders as follows:
AdvancedFiltering
AUTO-Fill PDF Forms
AutoSetTabOrderSaveLoadData
Calendars
Financial-ExpenseTracking
Find Date Get Row Number
Horizontal-VerticalTabs
HyperlinkFromDropDown
Miscellaneous
Misc-Websites
MultipleDropDownLists
Numbers2Words
Range-CellAddress-VLookUp-Text
RegexFormula
RGB-Colors
Security
SlideOutMenu

This list of sub-folders is likely to change, as in additional reasons for new folder names depending on some specific function etcetera.
Therefore I want to extract from the list in column B (acquired from Power Query) the sub-folders' name and have that appear in one cell, but not as I have shown in the merged cells that follow from column B.
Upon completion of this, I will have columns A & B hidden, and then any time I refresh the Power Query the changes show up on this sheet, including in addition to a new sub-folder but also a sub-folder name that was changed for whatever reason.
Forgot to mention. When I did the Power Query and edited the sheet I removed all columns showing dates and file names. I am not interested in the file names but only the folder names.
This worksheet includes a tab for each sub-folder where I have links to the various excel sheets, websites, and YouTubes associated with the specific sub-folder name.

ConsolidatedListSpecialExcel.xlsm
	A	B	C
1	Index	File Name
2	1	F:\Finances\CSOB2\SpecialExcelSheets\
3	2	F:\Finances\CSOB2\SpecialExcelSheets\
4	3	F:\Finances\CSOB2\SpecialExcelSheets\
5	4	F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\	AdvancedFiltering
6	5	F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\
7	6	F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\
8	7	F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\
9	8	F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\
10	9	F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\
11	10	F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\
12	11	F:\Finances\CSOB2\SpecialExcelSheets\AUTO-Fill PDF Forms\	AUTO-Fill PDF Forms
13	12	F:\Finances\CSOB2\SpecialExcelSheets\AUTO-Fill PDF Forms\
14	13	F:\Finances\CSOB2\SpecialExcelSheets\AUTO-Fill PDF Forms\
15	14	F:\Finances\CSOB2\SpecialExcelSheets\AUTO-Fill PDF Forms\
16	15	F:\Finances\CSOB2\SpecialExcelSheets\AUTO-Fill PDF Forms\
17	16	F:\Finances\CSOB2\SpecialExcelSheets\AutoSetTabOrderSaveLoadData\	AutoSetTabOrderSaveLoadData\
18	17	F:\Finances\CSOB2\SpecialExcelSheets\AutoSetTabOrderSaveLoadData\
19	18	F:\Finances\CSOB2\SpecialExcelSheets\Calendars\	Calendars
20	19	F:\Finances\CSOB2\SpecialExcelSheets\Calendars\
21	20	F:\Finances\CSOB2\SpecialExcelSheets\Calendars\
22	21	F:\Finances\CSOB2\SpecialExcelSheets\Calendars\
23	22	F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\	Financial-ExpenseTracking\
24	23	F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
25	24	F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
26	25	F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
27	26	F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
28	27	F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
29	28	F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
30	29	F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
31	30	F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
32	31	F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
33	32	F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
34	33	F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
35	34	F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
36	35	F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
37	36	F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
38	37	F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
39	38	F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
40	39	F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
41	40	F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
42	41	F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
43	42	F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
44	43	F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
45	44	F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
46	45	F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
47	46	F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
48	47	F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
49	48	F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
50	49	F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
FNames-Links

Cell Formulas
Range	Formula
A2:B50	A2	=VLOOKUP(FolderNames!A5,FolderNames!A:A,COLUMN(FolderNames!A:A)-COLUMN(FolderNames!A:A)+1,0)

Named Ranges
Name	Refers To	Cells
FolderNames!ExternalData_1	=FolderNames!$A$4:$B$163	A2:A50

 

[MrDB4Excel]

So the combined code would be as follows:?

let fxNewPaths = (t as table, i as number) =>
let
iActual = “L” & Text.From(i),
iNext = “L” & Text.From(i+1),
AddCol1 = Table.AddColumn(t, “Lx”, each try Table.SelectColumns(Table.AddColumn(Table.SelectRows(Folder.Contents(Record.Field(_, iActual)), each [Content] is table)[[Folder Path],[Name]], iNext, each [Folder Path] & [Name]), {iNext}) otherwise null),
Expand = Table.ExpandTableColumn(AddCol1, “Lx”, {iNext})
in
Expand,
FPath = Excel.CurrentWorkbook(){[Name=“FPath”]}[Content]{0}[Column1],
FirstRow = Table.FromColumns({{FPath}}, {“L0”}),
LiG = List.Generate(() => [FR = FirstRow, i = 0],
each List.NonNullCount( Table.Column([FR], “L”& Text.From([i]))) <> 0,
each [FR = fxNewPaths([FR], [i]), i = [i]+1],
each [FR]),
TBL = List.Last(LiG),
#”Unpivoted Columns” = Table.UnpivotOtherColumns(TBL, {}, “Attribute”, “Folder Path”),
#”Removed Columns” = Table.RemoveColumns(#”Unpivoted Columns”,{“Attribute”}),
#”Removed Duplicates” = Table.Distinct(#”Removed Columns”),
#”Sorted Rows” = Table.Sort(#”Removed Duplicates”,{{“Folder Path”, Order.Ascending}})
in
#”Sorted Rows”
#”Inserted Text After Delimiter” = Table.AddColumn(#”Sorted Rows”, “Text After Delimiter”, each Text.AfterDelimiter([Folder Path], “\”, {0, RelativePosition.FromEnd}), type text)

 

[sandy666]

let    fxNewPaths = (t as table, i as number) =>
        let
            iActual = "L" & Text.From(i),
            iNext = "L" & Text.From(i+1),
            AddCol1 = Table.AddColumn(t, "Lx", each try Table.SelectColumns(Table.AddColumn(Table.SelectRows(Folder.Contents(Record.Field(_, iActual)), each [Content] is table)[[Folder Path],[Name]], iNext, each [Folder Path] & [Name]), {iNext}) otherwise null),
            Expand = Table.ExpandTableColumn(AddCol1, "Lx", {iNext})
        in
            Expand,


    FPath = Excel.CurrentWorkbook(){[Name="FPath"]}[Content]{0}[Column1],
    FirstRow = Table.FromColumns({{FPath}}, {"L0"}),
    LiG = List.Generate(() => [FR = FirstRow, i = 0],
                    each List.NonNullCount( Table.Column([FR], "L"& Text.From([i]))) <> 0,
                    each [FR = fxNewPaths([FR], [i]), i = [i]+1],
                    each [FR]),
    TBL = List.Last(LiG),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(TBL, {}, "Attribute", "Folder Path"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
    #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Folder Path", Order.Ascending}}),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Sorted Rows", "Text After Delimiter", each Text.AfterDelimiter([Folder Path], "\", {0, RelativePosition.FromEnd}), type text)
in
    #"Inserted Text After Delimiter"

but I still suggest From Folder option if you want filenames

 

[MrDB4Excel]

Awesome help Sandy, thanks loads.
I will give this a shot someday when the need presents itself.
This code is copied and inserted into my Word doc for this subject.
One final question. You labeled this code with "Power Query:"
Am I to assume this code is to be inserted in some location during a power query edit session, such as indicated by these images:
Probably in the Advanced Editor?

 

[sandy666]

use Alt A PN O Q then Alt H Q
this is a place for the code

btw. pictures are too small to recognise something there

 

[MrDB4Excel]

Original images now inserted larger, are they any better?
I tried this use Alt A PN O Q then Alt H Q but it does not seem to get me to a place I recognize.
images follow of what I experienced doing this use Alt A PN O Q then Alt H Q
After Alt A PN I get this

After Alt A I get this

There is no PN
After Alt A then P nothing happens (probably is supposed to call up Power Query, but mu version of Excel is 2013).
This is where I think I should end up to insert the code:

 

[sandy666]

ops, 2013 with PQ add-in
so you need blank query, open advanced editor then replace code there with code copied from the post

btw. update your profile (Account Details) about Excel version

 

[MrDB4Excel]

Okay, now makes perfect sense

ops, 2013 with PQ add-in
so you need blank query, open advanced editor then replace code there with code copied from the post

btw. update your profile (Account Details) about Excel version

Profile updated, and then some.

 

[sandy666]

if you update Office to newer version some things will change
Thanks for updating profile