Power Query

[roopa_123]

Hi,

How can I create a table in power query which lists only the starting day of all months until current day?
For example: my start date is 1/1/2021 and end date is currentdate, how can I create a table which lists only the starting date of the month?
I would like to achieve this using powerquery only and not DAX 🙂
Thanks in advance!

[v-yiruan-msft · Accepted solution]

Hi @roopa_123 ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can apply the codes in your Advanced Editor:

let
  StDt = #date(2021, 1, 1), 
  AllDates = {Number.From(StDt) .. Number.From(Date.From(DateTime.LocalNow()))}, 
  StofMonthDates = List.Distinct(
    List.Select(
      List.InsertRange(List.Transform(AllDates, each Date.StartOfMonth(Date.From(_))), 0, {StDt}), 
      each Number.From(_) >= Number.From(StDt)
    )
  ), 
  #"Converted to Table" = Table.FromList(
    StofMonthDates, 
    Splitter.SplitByNothing(), 
    null, 
    null, 
    ExtraValues.Error
  ),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}})
in
  #"Renamed Columns"

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[roopa_123]

@v-yiruan-msft : Thanks for helping out with a solution! 

[truptis]

Hi @roopa_123 ,

You may try this:

https://youtu.be/XQItjpSasLQ 

[roopa_123]

Hi @truptis ,
The link provided shows how to generates all the dates, but according to my requirement, I only want to generate the date for first date of every month.