How to create Additional column with day no by filtering other column

[Anonymous]

Hi Everyone 

This is my dataset

I want to create one additional column which lists like day1, day2, day3 

example: If there is Chennai in the city column then it should consider the minimum of date in the date column and starts with day 1, day2, day3 and it should filter by keeping the city as a filter for populating day column.

My result should be

[camargos88]

Hi @Anonymous ,

I've created this file using Power Query:  Download PBIX 

If you consider it as a solution, please mark as a solution and kudos.

Ricardo

[Anonymous]

Hi @camargos88 

It really helped me a lot to learn more about it.

Suppose there are more duplicate date row then it displaying like 1, 2, 3, 4

But I need one day 1 for all the date and attached my example below

Please help me out from this.

[camargos88]

Hi @Anonymous ,

Check it:

I just sorted the table by date added an Index column, it won't have a duplicated index for the same market/city.

Ricardo

[Anonymous]

Hi @camargos88 

If there are 2 dates with the same sate then it should be populated as Day1, not day1, day2(as attached below).

In below attachment, you can see for the city Chennai 2 date are repeated but in day column it should be day 1 not day 1 and 2

[camargos88]

@Anonymous ,

I thought it was only till column:

Ricardo

[Anonymous]

Hi @camargos88 

Could you please share the answer for my question

[camargos88]

Hi @Anonymous 

The answer is to create nested groups and index:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8sxLyUxU0lFyzkjNy0vMBLKMDPQNjPWNDIBMUwOlWB2sagzhaoxMiFBjjKLGtzQ3CazEEGiVCVSJIQ4lhgglRjiUGMGVGEOUOJYWlxQl5oCVBVem5KVWQk0ygigzMcajzAiuzMgCQ5lvak5SfmlRXipIpTFCpSmuQEANzFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Market = _t, City = _t, Date = _t, Sales = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Market", type text}, {"City", type text}, {"Date", type date}, {"Sales", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Market", "City"}, {{"Rows", each _, type table [Market=text, City=text, Date=date, Sales=number, Index=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn(Table.Group([Rows],
{"Market", "City", "Date", "Sales"},
{
{"Rows", each _, type table
[
Sales=number
]
}
}), "Index", 1,1)),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Market", "City", "Date", "Rows", "Index"}, {"Market", "City", "Date", "Rows", "Index"}),
#"Expanded Rows" = Table.ExpandTableColumn(#"Expanded Custom", "Rows", {"Sales"}, {"Sales"})
in
#"Expanded Rows"

Also, you can download the pbix file.

If you consider it as a solution, please mark as a solution and kudos.

Ricardo

[Anonymous]

Hi @camargos88 

I couldn't find the link to download your pbix file

[camargos88]

@Anonymous ,

File:  Download PBIX 

Ricardo

[Anonymous]

Hi @camargos88 

Still, I couldn't reflect this logic into my report 

so I have attached my original sample dataset so please clarify by using this

[camargos88]

@Anonymous ,

Is it correct to have a day 3 skipping the day 2 on India / Delivery 2 ?

Ricardo

[Anonymous]

Yes, correct because day 2 will be 11th April 2020.

Day 1, 2, 3, 4 should calculate based on the first date

[camargos88 · Accepted solution]

Hi @Anonymous ,

Check the file:  Download PBIX 

Take a look at table 2, it creates a range of date for each row and an index to get the index value for the date range.

If you consider it as a solution, please mark as a solution and kudos.

Ricardo

[parry2k]

@Anonymous create a rank and a lot more details here.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!