cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RuanG
Frequent Visitor

Indexing using PowerQuery

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.

ClientNoDateIce CreamsRateIndexIndex From 0
12342019/12/01200001010
12342019/12/0110001021
12342019/12/0150001132
12342019/12/0220001243
12342019/12/0290001154
12342019/12/0250001365
12342019/12/0360001476
12342019/12/0460001287
12342019/12/0550001198
12582019/12/0520002010
12582019/12/0510001021
12582019/12/0560001532
12582019/12/0550001243
12582019/12/0610001354
12582019/12/0640001465
12582019/12/0650001576
12582019/12/0660002087
12582019/12/0640001098
12582019/12/0650011109

 

Thanks in advance !

 

Regards,

1 ACCEPTED SOLUTION
Jimmy801
Community Champion
Community Champion

Hello @RuanG,

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

View solution in original post

3 REPLIES 3
Jimmy801
Community Champion
Community Champion

Hello @RuanG 

 

any news?

 

Jimmy

Jimmy801
Community Champion
Community Champion

Hello @RuanG,

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

View solution in original post

stvsurf
Resolver I
Resolver I

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 

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors