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 there Power BI Community,
I have this data set and would like to repeat the [DateMonth] value of [CollectionType] = "First" where the [CollectionType] = "Recurring". I cannot use the fill-down function in this instance due to the layout of the data and am hoping to accomplish this in Power Query by adding a Custom Column.
My M code goes something along the lines of:
Lines 1 and Line 3 are fine. I need help to update line 2 please. so that the blanks in the custom column are replaced with the value of [DateMonth] that appears where Collection Type = "First"
if [Collection Type] = "First" then [DateMonth]
else if [Collection Type] = "Recurring" then insert the record of [DateMonth] that is equal to "First"
else null
Here is an example of the dataset:
Here is an example of what the data set should end up looking like:
Thanks in advance.
Solved! Go to Solution.
@BICrazy
Add the following custom column :
=
if [Collection Type] = "First" then
[DateMonth]
else if [Collection Type] = "Recurring" then
let
m = _[DateMonth],
p = _[#"Policy No#"]
in
Table.SelectRows(PreviousStep, each _[#"Policy No#"] = p and _[#"Collection Type"] = "First")[
DateMonth
]{0}
else
null
Replace the "PreviousStep" with the correct step name in your query
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@BICrazy
Add the following custom column :
=
if [Collection Type] = "First" then
[DateMonth]
else if [Collection Type] = "Recurring" then
let
m = _[DateMonth],
p = _[#"Policy No#"]
in
Table.SelectRows(PreviousStep, each _[#"Policy No#"] = p and _[#"Collection Type"] = "First")[
DateMonth
]{0}
else
null
Replace the "PreviousStep" with the correct step name in your query
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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.
User | Count |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |