Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Split/copy row (Year, Quarter, Month)

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

 

YearProductQuarterProductMonthProduct
A-2020A-Q1-2020A-Jan-2020
 A-Q1-2020A-Feb-2020
 A-Q1-2020A-Mar-2020
 A-Q2-2020A-Apr-2020
 A-Q2-2020A-May-2020
 A-Q2-2020A-Jun-2020
 A-Q3-2020A-Jul-2020
 

A-Q3-2020

A-Aug-2020
 

A-Q3-2020

A-Sep-2020
 A-Q4-2020A-Oct-2020
 A-Q4-2020A-Nov-2020
 A-Q4-2020A-Dec-2020
  • 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:

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

     

     

     

  • ziying35's avatar
    ziying35
    Icon for Impactful Individual rankImpactful Individual

    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:

     

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion

    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.

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion

    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"

     

     

    YearProductYearQrtYearMonth
    2020AA-2020-Q1A-2020-Jan
    2020AA-2020-Q1A-2020-Feb
    2020AA-2020-Q1A-2020-Mar
    2020AA-2020-Q2A-2020-Apr
    2020AA-2020-Q2A-2020-May
    2020AA-2020-Q2A-2020-Jun
    2020AA-2020-Q3A-2020-Jul
    2020AA-2020-Q3A-2020-Aug

    ...