Split/copy row (Year, Quarter, Month)

[Anonymous]

Hi, 

Need some help to figure this out....

All products in a product portfolio are valid for a certain time period, for example for the year 2020, the 1st quarter of 2020 and so on.   Certain products in the product portfolio need to be split into quarterly products from the parent annual contract and from the quarterly products into monthly products. What would be the optimal solution to achieve this, preferably in Power Query? 

In below example, product A-2020 is the base product (parent) and this needs to be split into 4 quarterly products and 12 monthly products.

Best regards,

Chris

YearProduct	QuarterProduct	MonthProduct
A-2020	A-Q1-2020	A-Jan-2020
	A-Q1-2020	A-Feb-2020
	A-Q1-2020	A-Mar-2020
	A-Q2-2020	A-Apr-2020
	A-Q2-2020	A-May-2020
	A-Q2-2020	A-Jun-2020
	A-Q3-2020	A-Jul-2020
	A-Q3-2020	A-Aug-2020
	A-Q3-2020	A-Sep-2020
	A-Q4-2020	A-Oct-2020
	A-Q4-2020	A-Nov-2020
	A-Q4-2020	A-Dec-2020

[CNENFRNL]

Gurus have provided preceding solutions above. Mine is for practice of List.Generate only

let
    Source = Table.FromColumns({{2020..2022}, {"A".."C"}}, {"Year", "Product"}),
    #"Added Custom" = Table.AddColumn(
        Source, "Custom",
        (EachRow as record) => List.Skip(
            List.Generate(
                () => 0, each _ <= 12, each _ + 1,
                each
                [y = EachRow[Year],
                q = Date.QuarterOfYear(#date(y, _, 1)),
                m = Date.ToText(#date(y, _, 1), "yyyy-MMM"),
                str = EachRow[Product] & "-" & Text.From(y) & "-Q" & Text.From(q) & ";" & EachRow[Product] & "-" & "-" & m][str]
            ),
            1
        )
    ),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.ExpandListColumn(#"Added Custom", "Custom"), "Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"YearQrt", "YearMonth"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"YearQrt", type text}, {"YearMonth", type text}})
in
    #"Changed Type"

Year	Product	YearQrt	YearMonth
2020	A	A-2020-Q1	A-2020-Jan
2020	A	A-2020-Q1	A-2020-Feb
2020	A	A-2020-Q1	A-2020-Mar
2020	A	A-2020-Q2	A-2020-Apr
2020	A	A-2020-Q2	A-2020-May
2020	A	A-2020-Q2	A-2020-Jun
2020	A	A-2020-Q3	A-2020-Jul
2020	A	A-2020-Q3	A-2020-Aug

...

[ziying35 · Accepted solution]

@Anonymous 

Here's a custom function I wrote, which can use either text or list arguments:

// fnGen
(YearProduct)=>
let
   fx =(str)=>
   let
     split = Text.Split(str,"-"),
     rec = List.Transform(
            {0..11}, 
            each let 
                   lst={
                         str, 
                         split{0}&Number.ToText(Number.IntegerDivide(_, 3)+1, "-Q0-")&split{1}, 
                         split{0}&Date.ToText(#date(Number.From(split{1}), _+1, 1), "-MMM-yyyy", "en-US")
                       }
                    in Record.FromList(lst, {"YearProduct", "QuarterProduct", "MonthProduct"})
           )
   in
     rec,
     gen = List.Transform((if YearProduct is list then YearProduct else {YearProduct}), fx),
     result = Table.FromRecords(List.Combine(gen))
in
     result

The way to use it is shown below:

[Anonymous]

let
    y2q2m = (prod as text, year as text)=>
    let 
    
    q2m= List.Transform({1..4},    (q)=> [qProd= prod&"-Q"&Text.From(q)&"-"&year ,mProd= List.Transform({1..3}, each prod&"-"&Date.MonthName(#date(2020,_+(q-1)*3,1),"en-US")&"-"&year)]) 
   
    in Table.FromColumns({{year},{q2m}},{"yProd","qmProd"})
in
    y2q2m

invoking the function

let
    Source = y2q2m("P", "2020"),
    #"Expanded qmProd" = Table.ExpandListColumn(Source, "qmProd"),
    #"Expanded qmProd1" = Table.ExpandRecordColumn(#"Expanded qmProd", "qmProd", {"qProd", "mProd"}, {"qProd", "mProd"}),
    #"Expanded mProd" = Table.ExpandListColumn(#"Expanded qmProd1", "mProd")
in
    #"Expanded mProd"

get the table

[Greg_Deckler]

@Anonymous - So what are you starting with, a list of quarterly periods and a list of monthly periods? What do those base tables look like? Are they related?

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.