How do I create a column of data using a single cell of source data

[Frenchtom811]

Hi folks!  I am connecting to a folder that contains 2 dozen excel files.  Each file is idential in layout, the only difference is that some files have about 7,000 rows of data while others have over 10,000.  I doubt this is relevant.

The attched screen grab shows a representative sample with only 16 rows of data.  What I need to do it take the value in cell B1 and B2 in all 24 file instances and create two new columns of data that contain these values.  So referring to my screen grab, cell H7 should say Customer Number while cells H8 through H16 say ABC123.  Cell I7 should say Customer Name while cells I8 through I16 say APAC Distributor.  I need this to happen for each of the 2 dozen files I am connecting to meaning that the customer name and number should be specific to each source file.  Any idea how to do this?

[Anonymous]

You can do this in the Transform File. Make your next step after the source steps be = PreviousStepName[Column2]{0}. Name that step CustomerNumber. Then make your next step's formula like:

= StepNameBeforeTheLastStep

Now you have your original table, and the CustomerNumber as a variable. You can use CustomerNumber in an AddColumn step, and that formula would be:

Table.AddColumn(PriorStepName, "Customer Number", each CustomerNumber)

--Nate

[Frenchtom811]

Hello @Anonymous .  The refresh produced one value for customer number across all of my 2 dozen excel files.  This value came from the very first file.  I need the customer number to be syncronized to each individual file.  I'll keep working on this and restore your recommendation if I can get it to work.  

[Frenchtom811]

Hello @Anonymous !  I found a similar solution using Record.Field and pulling the recorded value into a custom column.  I think your solution is very similar and like most things in Power BI, is one of many solutions to a problem.  I got the Record.Field idea from the following website:

https://exceleratorbi.com.au/convert-a-cell-value-into-a-column-with-power-query/

This site is a bit dated and I am testing a more straightforward approach as I type but it seems like it is working.

[artemus]

You would use List.Generate:

List.Generate(() => myCell, each <<logic to test when to stop>>, each <<generate next value>>)

[Frenchtom811]

Hello @artemus , is this function located within Query Editor?  If yes, where?  If not, how do I execute this command?  Thanks!