Fiscal Month Start-, End Date and Day Number

[joshua1990]

I am currently working on moving my DAX-generated calendar to Power Query, as this has many advantages.
I'm just about through converting such a Fiscal Calendar based on the 445-approach. In the meantime, I have been able to convert the Fiscal Year, Fiscal Quarter, Fiscal Month, and Fiscal Week.

What is missing is the start and end date per Fiscal Month as well as the respective day of the Fiscal Month.
The structure of the calendar is as follows:

Date	Fiscal Week	Fiscal Month	Fiscal Quarter	Fiscal Year
28.12.2020	53	12	4	2020
29.12.2020	53	12	4	2020
30.12.2020	53	12	4	2020
31.12.2020	53	12	4	2020
01.01.2021	53	12	4	2020
02.01.2021	53	12	4	2020
03.01.2021	53	12	4	2020
04.01.2021	1	1	1	2021
05.01.2021	1	1	1	2021
06.01.2021	1	1	1	2021
07.01.2021	1	1	1	2021
08.01.2021	1	1	1	2021
09.01.2021	1	1	1	2021

[edhans]

What is your expected result here @joshua1990 

Not really knowing, I have put the first and last date in the table you provided per month/year into a Start/End date column:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jcpBCkAhCATQu7gO0an+r7NE979G4qpVCs6Aw1uLMFjBEAgV6tVKYdUsPu5iZsamSsJobETZzl59GCRMTZh2mTs+ueih+ELxh2KEYj7FPg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Fiscal Week" = _t, #"Fiscal Month" = _t, #"Fiscal Quarter" = _t, #"Fiscal Year" = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-BS"),
    #"Grouped Rows" = 
        Table.Group(
            #"Changed Type with Locale", 
            {"Fiscal Month", "Fiscal Year"}, 
            {
                {"Start Date", each List.Min([Date]), type date},
                {"End Date", each List.Max([Date]), type date},
                {
                    "AllRows", each _, type table [Date=nullable date, Fiscal Week=nullable text, Fiscal Month=nullable text, Fiscal Quarter=nullable text, Fiscal Year=nullable text]
                }
            }   
        ),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Date", "Fiscal Week", "Fiscal Quarter"}, {"Date", "Fiscal Week", "Fiscal Quarter"})
in
    #"Expanded AllRows"

This:

becomes this:

If you need something else, please provide exact requirements.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[joshua1990]

@edhans : Thank you so much, this is exactly the result that I am looking for. Unfortunately, this approach will not work for my calendar. The calendar contains overall more than 20 columns with specific information. If I would use your approach with the grouping function all data will be gone. 

[edhans · Accepted solution]

No it won't @joshua1990 . Everything will be preserved. Notice in the code the "Fiscal Week" is "gone" in the Grouped Rows step. It is hidden in the ALL ROWS nested table. At the end of the process the "Expanded AllRows" step brings it back. It will bring back 1 coluimn, 20 columns, or 200 columns. Nothing is lost. Same with Fiscal Quarter.

1. all data in table
   1. 
2. Grouped data based on Month and Year, adding Start and End date. The other columns are preserved in AllRows
   1. 
3. Hidden columns come back in the expansion step. Nothing lost.
   1. 

The columns are in a different order. You can move them if you want, but I never bother. They go into Power BI's model alphabetically anyway.