Power Query - Add Custom Column Depends on Reporting Periods-CodePudding

I have a dataset with the Company name, Year, Reporting Starting Date and Reporting Ending Date. The reporting periods are categorized into 4 quarters. I would like to add Quarter names depending on the reporting period. My dataset is,

I would like to add a column like the below,

See, Reporting period starting date would not need to be the same for every company. It could vary depending on the company. But I would like to add the quarter column depends on the company and year.

The Q prefix is not mandatory.

I would like to get the new column depending on the Company, Year, and Period Start.

CodePudding user response：

Sounds like all you want to do is add 1/2/3/4 in sequence by Company

If you wanted to do this in powerquery, load the data (data .. from table/range [x]headers) then right click the Company column and Group by. Choose operation All Rows and accept defaults. In the formula bar (or home advanced editor) change the end of the code from

 each _, type table ...

to be

each Table.AddIndexColumn(_, "Quarter", 1, 1), type table}})

Then use arrows atop column to expand

Sample full code

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Company"}, {{"Data", each Table.AddIndexColumn(_, "Quarter", 1, 1), type table}}),
#"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Year", "Period Start", "Period End", "Quarter"}, {"Year", "Period Start", "Period End", "Quarter"})
in #"Expanded Data"