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.
Deleted.
Solved! Go to Solution.
Yes @Mehal1996 - try this:
You cannot add fractions of months, so I had to convert your months to average days, assuming each month has 30.4 days in it (365/12).
It returns this:
the start range is the average days in the first range of months (3/6/9) and End range is the last set of months (6/9/12). I then took the average of those two, rounded to 0 decimals, and added that number of days to the first value.
At this point, remove the columns you don't need. Here is the full code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDXNzDTNzIwMlTSUTJW0FUwU/DNzyvJKFaK1YlWMjRGkjUDyloiyxoZIMlaAmUNjeDSsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Lead Created Date:" = _t, #"Timeframe to Buy:" = _t]),
#"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Lead Created Date:", type date}}, "en-BS"),
#"Added Start Range" = Table.AddColumn(#"Changed Type with Locale", "Start Range", each Number.From(Text.BeforeDelimiter([#"Timeframe to Buy:"], "-")) * 30.4),
#"Added End Range" = Table.AddColumn(#"Added Start Range", "End Range", each Number.From(Text.BeforeDelimiter(Text.AfterDelimiter([#"Timeframe to Buy:"], "-"), "Months")) * 30.4),
#"Inserted Average" = Table.AddColumn(#"Added End Range", "New Date", each Date.AddDays([#"Lead Created Date:"], Number.Round(List.Average({[Start Range], [End Range]}), 0)), type date)
in
#"Inserted Average"
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYes @Mehal1996 - try this:
You cannot add fractions of months, so I had to convert your months to average days, assuming each month has 30.4 days in it (365/12).
It returns this:
the start range is the average days in the first range of months (3/6/9) and End range is the last set of months (6/9/12). I then took the average of those two, rounded to 0 decimals, and added that number of days to the first value.
At this point, remove the columns you don't need. Here is the full code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDXNzDTNzIwMlTSUTJW0FUwU/DNzyvJKFaK1YlWMjRGkjUDyloiyxoZIMlaAmUNjeDSsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Lead Created Date:" = _t, #"Timeframe to Buy:" = _t]),
#"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Lead Created Date:", type date}}, "en-BS"),
#"Added Start Range" = Table.AddColumn(#"Changed Type with Locale", "Start Range", each Number.From(Text.BeforeDelimiter([#"Timeframe to Buy:"], "-")) * 30.4),
#"Added End Range" = Table.AddColumn(#"Added Start Range", "End Range", each Number.From(Text.BeforeDelimiter(Text.AfterDelimiter([#"Timeframe to Buy:"], "-"), "Months")) * 30.4),
#"Inserted Average" = Table.AddColumn(#"Added End Range", "New Date", each Date.AddDays([#"Lead Created Date:"], Number.Round(List.Average({[Start Range], [End Range]}), 0)), type date)
in
#"Inserted Average"
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi Ed,
The code that you've provided above for this, is this an M code which goes into a blank query? I'm just coming back to this ask and seeing how I can put this solution into practice. Thanks!
Regards,
Mehal
It is M code.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering 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.