Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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:
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 |
...
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 still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@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:
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
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.