Adding an index column and log index numbers

[NimaM]

Hi,

I've created a solution for ETL using Power Query. The solution pulls in data from various sources and does the transform steps and finally uses Append to create an output dataset, which I can then plan to upload to a database. As this is a routine task, I'd like to add an index column to the "output" dataset, which is still easy, however, I need to somehow keep track of the last index number (for any batch) so that my next run of the solution uses the last index from the previous one and continues from there. The end game here is to fully automate the process so I don't have to manually feed the next index. I hope this is a clear explanation. 

Thank you for your help!

[v-jingzhang]

Hi @NimaM 

I'm not sure if it's possible to log the last index number in Power Query. Since you plan to upload the output dataset to a database, it may be easier to add an auto increment index column in the database. SQL Auto Increment - Defining Auto Increment Column for a Table (sqltutorial.org)

Best Regards,
Community Support Team _ Jing

If this post helps, please Accept it as Solution to help other members find it.

[NimaM]

Thanks for the hint. follow up questions:

1- How to check if exist? I know in SQL, but not in the Power Query. 

2- How to auto log the last index number so that it can be continued from there?

3- is there any chance this can be done with the input parameter? 

[AlB]

@NimaM 

Answers to your questions:

1. You can a) read the input table column names and b) see if the index colum is amongst them. Place the following M code in a blank query to see it at work (last step is the relevant one) 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUVKK1YlWcoIxnGEMFzAjFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DataCol = _t, IndexCol = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DataCol", type text}, {"IndexCol", type text}}),
    indexExists_ = List.Contains(Table.ColumnNames(#"Changed Type"), "IndexCol")
in
    indexExists_

2. If you delete the previous index in the main table, append the new data and then add a new index column, you don't to keep track of the previous last index number. Example:

Input data, piece 1

Col1

A

B

Input data, piece2

Col1

C

D

E

First run (add piece 1) Index does NOT exist, so we load the data and add index. Output table:

Col1	IndexCol
A	1
B	2

Second  run (add piece 2). Index DOES exist in the ouput table from the first run, so first we delete it and then append the new data:

Col1

A

B

C

D

E

and finally add the index. Output table:

Col1	IndexCol
A	1
B	2
C	3
D	4
E	5

3. N/A with the solution above

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[AlB]

Hi @NimaM 

There are other ways but perhaps the simplest solution would be to, in each run, check if your index column exists (it only will not exist in the very first run) and:

-if it doesn't, add a new index column

- if it does, delete it and add a new index column.

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.