Table.TransformColumnNames in nested table

[ooptennoort]

I need to replace ":" with "" in column names but in a nested table (that I cannot expand yet)... 2 problems:

1) How do I get a nested table as table (1st argument of Table.TransformColumnNames)
2) Then how do i actually get the column names' text replaced

I tried along these lines:

= Table.TransformColumnNames( Table.FromList(RemovedTopRowPromotedHeaders[Data], Splitter.SplitByNothing(), null, null, ExtraValues.Error), (_) as text => Text.Clean(Text.Replace(_,":","")))

= Table.AddColumn(Custom2, "RecordToList", each List.ReplaceValue(Record.ToList([Data]{1}),":","",Text.Replace))

even:
RecordsToList = Table.AddColumn(#"Removed Other Columns1", "RecordsToList", each Record.ToList([Data]{1})),
RenRecordsToList = Table.AddColumn(RecordsToList, "RenRecordsToList", each List.ReplaceValue([RecordsToList],":","", Replacer.ReplaceText)),
Custom2 = Table.RenameColumns([Data], [RecordsToList],[RenRecordsToList] ),
AddRemTopRowHeaderPromoted = Table.AddColumn(Custom2, "Ranges", each Table.PromoteHeaders( Table.Skip([Data],1))),

 

[alansidman]

See if this works for you

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"1:00", type text}, {"2:00", type text}, {"3:00", type text}, {"4:00", type text}, {"5:00", type text}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Replaced Value" = Table.ReplaceValue(#"Transposed Table",":","",Replacer.ReplaceText,{"Column1"}),
    #"Transposed Table1" = Table.Transpose(#"Replaced Value"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true])
in
    #"Promoted Headers"

Book5
	A	B	C	D	E	F	G	H	I	J	K
1	1:00	2:00	3:00	4:00	5:00		100	200	300	400	500
2	B	B	B	B	B		B	B	B	B	B
3	C	C	C	C	C		C	C	C	C	C
4	D	D	D	D	D		D	D	D	D	D
5	E	E	E	E	E		E	E	E	E	E
6	F	F	F	F	F		F	F	F	F	F
7	G	G	G	G	G		G	G	G	G	G
8	H	H	H	H	H		H	H	H	H	H
9	I	I	I	I	I		I	I	I	I	I
10	J	J	J	J	J		J	J	J	J	J
11	K	K	K	K	K		K	K	K	K	K
Sheet1

Cell Formulas
Range	Formula
A2:E11	A2	=SUBSTITUTE(ADDRESS(1,ROW(),4),"1","")

 

[ooptennoort]

See if this works for you

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"1:00", type text}, {"2:00", type text}, {"3:00", type text}, {"4:00", type text}, {"5:00", type text}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Replaced Value" = Table.ReplaceValue(#"Transposed Table",":","",Replacer.ReplaceText,{"Column1"}),
    #"Transposed Table1" = Table.Transpose(#"Replaced Value"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true])
in
    #"Promoted Headers"

Book5
	A	B	C	D	E	F	G	H	I	J	K
1	1:00	2:00	3:00	4:00	5:00		100	200	300	400	500
2	B	B	B	B	B		B	B	B	B	B
3	C	C	C	C	C		C	C	C	C	C
4	D	D	D	D	D		D	D	D	D	D
5	E	E	E	E	E		E	E	E	E	E
6	F	F	F	F	F		F	F	F	F	F
7	G	G	G	G	G		G	G	G	G	G
8	H	H	H	H	H		H	H	H	H	H
9	I	I	I	I	I		I	I	I	I	I
10	J	J	J	J	J		J	J	J	J	J
11	K	K	K	K	K		K	K	K	K	K
Sheet1

Cell Formulas
Range	Formula
A2:E11	A2	=SUBSTITUTE(ADDRESS(1,ROW(),4),"1","")

Thank you! Very simple method indeed, along really different lines
I will look into it to see how I can transpose in a nested table. Shouldn't be difficult. Thank you!