Custom columns in Power query(uniqe ID )

Please update your profile with your current version of Excel.

Hi

bring in your source to PQ Editor, Create custom column, [Account]&[Document Number], Group by this custom column, New Column name as Data,
operation select All rows. Then create another custom column

Table.AddIndexColumn([Data],"Index",1,1)

The same concat Account&Document will have Index 1,2,3........... encap side the table, expand and extract all the original fields and the last two colmns

Please Login or Register  to view this content.

Andrew - please update your profile with your current version of Excel.

Hi Andrew,

Step1: place your cursor in the data area, goto Data （Get and Transform) > From Table > Select Range A2:P39244,
Step2: In Power Query Editor, goto home > Advanced Editor, cut and paste the code to replace
you can understand the code look at the applied steps at the right-hand side Query Settings



I got problem in uploading xlsb (2.7MB), xlsx(7.5MB) yesterday, attached is the first 100 rows of your records, you can just cut and paste
your 39244 records and refresh 2nd tab Power query result

At last! Thanks.

Hi Andrew,

1) so far only 30K records and it should not take more than 1 minute, I even process few millions records
2) Yes, you can name directly in the custom column
3) Index column in Power query use for many other scenarios like ranking, running total, extract previous column fields etc, sumif, sumifs, countif, countifs are Group by in Power query, it is quite powerful, can be used for extracting aggregation (sum, count, max, min, average) etc

I suggest you google PQ related video, website to learn slowly ( I did it some route), if not can pay for elearning PQ courses to expedite your learning

However, it is individual preference, you can still use native excel command to achieve the same results, and if the datasets are huge (near 1 millions, you will still encounter slowest, I use PQ because it can handle more than 1 million records ( and it is not that slow as laptop is not a server) using in memory technology, and I only need to load the result (output summary) to excel and not the entire source files ( anyway > 1 mil also can't load to excel), and the file size is quite small

so for your case, probably only load those column that your require, another method will be open a new workbook, data>New query> from Sheet > ..... and bring into PQ and transform inside the PQ Editor, load only the results, then the file will be very small and processing will be much more faster

Cheers