Forum Discussion
Adding an index column and log index numbers
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?
Community ChampionAnswers 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. |