cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kfreitass
Helper I
Helper I

Index

 

Hello everybody. Could someone help me? I have a Customers table where I have concatenated the Month and Year and I would like to create an index for each Month/Year without considering the duplicates. How could I do this?

kfreitass_0-1635827842169.png

 Thanks guys

 

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi, @kfreitass ;

You could create index column then sort by group , as follows:

1.sort by date column.

vyalanwumsft_1-1636006685974.png

 

2.add index column.

vyalanwumsft_0-1636006671541.png

3.group by the date colum("all rows")

vyalanwumsft_2-1636006765196.png

4.removing other columns.

vyalanwumsft_3-1636006999809.png

5.Expand Column.

The final output is shown below:

vyalanwumsft_4-1636007113690.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldFNCoAgEAXgu7gWGi0rzyIu+vEAURTdviIcgydC2483w2PGOUGqUlRp0iSkGISXL6lII5OOtEei+iF104QUkGYmU6CDqcP1Lab6SCuTxUGLvRoswbQgZQYztCGdSOZfKr2DU+lpoXRC8zmOvwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Date"}, {{"Count", each _, type table [Date=nullable date, name=nullable text, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index2",1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Date", "name", "Index2"}, {"Date", "name", "Index"})
in
    #"Expanded Custom"

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-yalanwu-msft
Community Support
Community Support

Hi, @kfreitass ;

You could create index column then sort by group , as follows:

1.sort by date column.

vyalanwumsft_1-1636006685974.png

 

2.add index column.

vyalanwumsft_0-1636006671541.png

3.group by the date colum("all rows")

vyalanwumsft_2-1636006765196.png

4.removing other columns.

vyalanwumsft_3-1636006999809.png

5.Expand Column.

The final output is shown below:

vyalanwumsft_4-1636007113690.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldFNCoAgEAXgu7gWGi0rzyIu+vEAURTdviIcgydC2483w2PGOUGqUlRp0iSkGISXL6lII5OOtEei+iF104QUkGYmU6CDqcP1Lab6SCuTxUGLvRoswbQgZQYztCGdSOZfKr2DU+lpoXRC8zmOvwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Date"}, {{"Count", each _, type table [Date=nullable date, name=nullable text, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index2",1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Date", "name", "Index2"}, {"Date", "name", "Index"})
in
    #"Expanded Custom"

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

TheoC
Super User
Super User

Hi @kfreitass 

 

In Power Query you can use Group By and then use Index Column (under Add Column) to add the index accordingly 🙂

Below is where you can find "Group By" in Power Query.

TheoC_0-1635831237190.png

And here is where you can find Index:

TheoC_1-1635831261283.png

Hope this helps.

Theo

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Hi @TheoC 

Thanks for the reply.

When I use group by my other columns are affected, I end up losing my other columns or maybe I don't know how to group

 

Hi @kfreitass 

 

You can duplicate the table if you want both views 🙂

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

 I will try. Thanks so much @TheoC 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!