Indexing using PowerQuery

[Anonymous]

Good day,

Please could someone tell me if it is possible to Index columns.  I need two columns, one Index column starting at 0 and the other starting at 1.  The only issue is it needs to be grouped by Client no.

Please see below example.

ClientNo	Date	Ice Creams	Rate	Index	Index From 0
1234	2019/12/01	20000	10	1	0
1234	2019/12/01	1000	10	2	1
1234	2019/12/01	5000	11	3	2
1234	2019/12/02	2000	12	4	3
1234	2019/12/02	9000	11	5	4
1234	2019/12/02	5000	13	6	5
1234	2019/12/03	6000	14	7	6
1234	2019/12/04	6000	12	8	7
1234	2019/12/05	5000	11	9	8
1258	2019/12/05	2000	20	1	0
1258	2019/12/05	1000	10	2	1
1258	2019/12/05	6000	15	3	2
1258	2019/12/05	5000	12	4	3
1258	2019/12/06	1000	13	5	4
1258	2019/12/06	4000	14	6	5
1258	2019/12/06	5000	15	7	6
1258	2019/12/06	6000	20	8	7
1258	2019/12/06	4000	10	9	8
1258	2019/12/06	500	11	10	9

Thanks in advance !

Regards,

[Jimmy801]

Hello @Anonymous 

any news?

Jimmy

[Jimmy801 · Accepted solution]

Hello @Anonymous,

you have to Group the data and then apply TransformColumns twice

Here the solution for you

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdBRDsAQDAbgu3iW0GplziLuf43Zwma040UkX/x/m7MBDGSsQQ/JAToP96OeeoM3xcoEtoKbAFFgi7kEqiJt/+gpQRShPmITJAoahNyDhVn4mEWbBb0q5o2tovdgVfDU9CvikBJUQdM+VsG/PeLbVJl2TNEFPystJw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ClientNo = _t, Date = _t, #"Ice Creams" = _t, Rate = _t]),
    #"Changed Type" = Table.TransformColumnTypes
    (
        Source,
        {{"ClientNo", Int64.Type}, {"Date", type date}, {"Ice Creams", Int64.Type}, {"Rate", Int64.Type}}
    ),
    #"Grouped Rows" = Table.Group
    (
        #"Changed Type", 
        {"ClientNo"}, 
        {{"AllRows", each _, type table [ClientNo=number, Date=date, Ice Creams=number, Rate=number]}}
    ),
    AddIndex0 = Table.TransformColumns
    (
        #"Grouped Rows",
        {{"AllRows", each Table.AddIndexColumn
        (
            _,
            "Index0",
            0
        )}}
    ),
    AddIndex1 = Table.TransformColumns
    (
        AddIndex0,
        {{"AllRows", each Table.AddIndexColumn
        (
            _,
            "Index1",
            1
        )}}
    ),
    #"Expanded AllRows" = Table.ExpandTableColumn(AddIndex1, "AllRows", {"Date", "Ice Creams", "Rate", "Index0", "Index1"}, {"Date", "Ice Creams", "Rate", "Index0", "Index1"})

in
    #"Expanded AllRows"

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[stvsurf]

First you want to do a "group by", with "All rows" as your operation, and you can give the column a name. e.g. "group"

Then add a custom column with the formula:

= Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([group],"Index"))

Next you want to expand the index column of the new table