Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
NimaM
Frequent Visitor

Adding an index column and log index numbers

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!

4 REPLIES 4
v-jingzhang
Community Support
Community Support

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
Frequent Visitor

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
Community Champion
Community Champion

@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

 

SU18_powerbi_badge

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
Community Champion
Community Champion

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.

 

SU18_powerbi_badge

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.

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.