cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
celenius
Helper I
Helper I

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
1 ACCEPTED SOLUTION
ziying35
Impactful Individual
Impactful Individual

@celenius 

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:

122.png123.png

 

View solution in original post

4 REPLIES 4
CNENFRNL
Super User
Super User

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

...


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension!

DAX is simple, but NOT EASY!

ziying35
Impactful Individual
Impactful Individual

@celenius 

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:

122.png123.png

 

Rocco_sprmnt21
Community Champion
Community Champion

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

 

 

image.png

 

Greg_Deckler
Super User
Super User

@celenius - 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.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors