cancel
Showing results for
Did you mean:
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

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

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 4
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"}),
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

...

 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!
Impactful Individual

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:

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

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

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Learn Power BI 2nd Edition

Announcements