Formula emulation

To achieve the same result in Power Query Editor, you can follow these steps:

1. Load the text file into Power Query Editor by going to "Data" tab and selecting "From Text/CSV".

2. In the "Navigator" pane, select the file and click on "Edit".

3. In the Power Query Editor, go to "Add Column" tab and select "Conditional Column".

4. In the "Conditional Column" dialog box, create a new column named "Quantity" and enter the following formula:

= if [ID] = #"Previous Row"[ID] then if [Support] = #"Previous Row"[Support] then #"Previous Row"[Quantity] + 1 else 1

else 1

5. This formula checks if the ID and Support in the current row are the same as the previous row. If they are, it increments the Quantity by 1. Otherwise, it resets the Quantity to

6. Click "OK" to create the new column "Quantity".

7. Go to "Add Column" tab again and select "Custom Column".

8. In the "Custom Column" dialog box, create a new column named "Result" and enter the following formula:

= [Account] & "-" & Text.From([TYPE]) & "-" & Text.From([Quantity])

9. This formula concatenates the Account, TYPE, and Quantity columns into a single string

10. Click "OK" to create the new column "Result".

11. Finally, go to "Home" tab and select "Close & Load" to load the data into Excel.

The resulting table should have the same structure and values as the one you created in Excel.