Does anyone know how to create a date value for each row and divide into 3 values based on quarters

[Anonymous]

Hello, this is my table.  

Region	Product	Sub-product	Attribute	Value
Czech Republic	Furniture	Chair	Q1	12899
Czech Republic	Furniture	Chair	Q2	15929
Czech Republic	Furniture	Chair	Q3	12123
Czech Republic	Furniture	Chair	Q4	12093

How do I convert from this table to this one? Splitting quaters into 3, there should be month column.

Region	Product	Sub-product	Attribute	Value	Month/Date
Czech Republic	Furniture	Chair	Q1	4299.7	2022.01.01
Czech Republic	Furniture	Chair	Q1	4299.7	2022.02.01
Czech Republic	Furniture	Chair	Q1	4299.7	2022.03.01
Czech Republic	Furniture	Chair	Q2	5309.7	2022.04.01
Czech Republic	Furniture	Chair	Q2	5309.7	2022.05.01
Czech Republic	Furniture	Chair	Q2	5309.7	2022.06.01

and so on. I have different countries, products and subproduct as well. 

How do I achieve this on Power Query?

Thanks for your support.

[ImkeF · Accepted solution]

Hi @Anonymous ,
you can extract the number part from the quarter-column and then create a list of months based on that.
Expand that list out and divide by 3 to get monthly values:

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Region ", type text}, {"Product", type text}, {"Sub-product", type text}, {"Attribute", type text}, {"Value", Int64.Type}}),
    InsertBase = Table.AddColumn(#"Changed Type", "Base", each Number.From(Text.AfterDelimiter([Attribute], "Q")), type number),
    #"Added Custom" = Table.AddColumn(InsertBase, "Months", each {([Base]*3-2) .. [Base]*3}),
    #"Expanded Months" = Table.ExpandListColumn(#"Added Custom", "Months"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Months", "MonthlyValue", each [Value]/3)
in
    #"Added Custom1"

Please also check file enclosed.