Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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.
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?
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. |
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. |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.