Power Query appending (different cells from) same table in sample file

[ooptennoort]

I have >100 files, all with the same make-up. I use Sample File to get a 'specific part of the table'. I also need '1 value' of this table from a specific cell, BUT this value is filtered out in the steps to get the 'specific part of the table'.

I could Add (the '1 value') as New Query before I take the steps (to get the 'specific part of the table'), turn it into a table, and then Append this to the Sample File BUT... this will result in the hard coded '1 value'.

Any one dealt with this before? How do I get the '1 value' to stay dynamic i.e. to stay (as it where) in the Sample File? (I rely heavily on the interface, still, so solve this M wise myself is still a bridge too far.)

 

[GraH]

A bit to cryptic I'm afraid. Can't you share a sample table with sanitized data. Please no pictures. Use the xl2bb add-on.

 

[ooptennoort]

A bit to cryptic I'm afraid. Can't you share a sample table with sanitized data. Please no pictures. Use the xl2bb add-on.

I realise it's cryptic, but it is private data so I'll have need a little time to de-privatise (what's the proper term) or get permission to share, if that's ok. (Does sanitizing include de-privatising data?)
Is there a specific part that I can maybe clear up in the mean time?

 

[GraH]

(Does sanitizing include de-privatising data?)

In my book it does.

Is there a specific part that I can maybe clear up in the mean time?

Showing the table structure and highlighting the special cell may help.

 

[ooptennoort]

I need the table below the top 7 rows (blue = headers). I do this in the Sample File because I have >100 of such tables. But I also need the highlighted name in C4, ALSO in the Sample File. Now it seems I can only do either of these (because there is only one Sample File). (Hope I am making sense.) There must be a trick, but how??

	Title		code
	Naam:	Mark
	Cluster:
	Datum:	23-6-2020

 

[GraH]

In your sample you can add a column and with an if statement get the name out of that cell. Fill it down.
The trick will be to get the column name dynamic when you promote headers of your Blue table.
Without excel for the moment, but I'll have a look later. It is doable and not super complicated.

 

[ooptennoort]

I got it!
I drilled down (C4) before promoting headers to get dynamic reference and added this in a column (hard coded with Name) after (!) promoting headers: = Table.AddColumn(#"Promoted Headers", "Name", each #"Sheet3"{2}[Column2]). Thanks for your help!!

 

[GraH]

Well done ooptennoort, I missed your earlier message, so I did go with your table from #5 and came up with something like below.

Table.AddColumn(Source, "Script", each
let
AddName = Table.AddColumn([Data],"TempName", each if [Column1]="Naam:" then [Column2] else null),
Fill = Table.FillDown (AddName, {"TempName"} ),
SkipTop6 = Table.Skip(Fill, 6),
OriginalHeaders= List.FirstN(Record.ToList(SkipTop6{0}),7),
Headers = List.Combine({List.FirstN(OriginalHeaders ,6), {"Naam"}}),
GetTable = Table.Skip(SkipTop6,1),
ToRename = Table.ColumnNames(GetTable),
ResTable = Table.RenameColumns(GetTable, List.Zip({ToRename,Headers}))
in 
 ResTable)

 

[ooptennoort]

Well done ooptennoort, I missed your earlier message, so I did go with your table from #5 and came up with something like below.

Table.AddColumn(Source, "Script", each
let
AddName = Table.AddColumn([Data],"TempName", each if [Column1]="Naam:" then [Column2] else null),
Fill = Table.FillDown (AddName, {"TempName"} ),
SkipTop6 = Table.Skip(Fill, 6),
OriginalHeaders= List.FirstN(Record.ToList(SkipTop6{0}),7),
Headers = List.Combine({List.FirstN(OriginalHeaders ,6), {"Naam"}}),
GetTable = Table.Skip(SkipTop6,1),
ToRename = Table.ColumnNames(GetTable),
ResTable = Table.RenameColumns(GetTable, List.Zip({ToRename,Headers}))
in
 ResTable)

Which do you think is better? And, more importantly, why? (If I may ask more of your time.)

 

[GraH]

If yours works, it works for you. At least you understand what you did, and you are able to manage that when changes are required.
You can compare both codes. I tried to avoid hardcoding except for the temporary added column name.