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,
Im working with dirty data, missing rows, scattered columns!
i'll not complain more 🙂 ,
so trying to add data a custom column, i was able to achive this in excel using calculated column "X"
X4 =IF(A4= "teTNCSMV123",F4,X3)
X5 =IF(A5= "teTNCSMV123",F5,X4)
i am able to pickup first value (if True), is there anyway to get the second value (if False) ?
is it possible to achive this in Power BI ?
Thank you
Van
Solved! Go to Solution.
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Coluxn1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Manual TAG", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Start([Coluxn1],5)="HdDte" then [Column6] else null), #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}), #"Added Custom1" = Table.AddColumn(#"Filled Down", "Custom.1", each [Manual TAG]=[Custom]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom.1"}) in #"Removed Columns"
Hope this helps.
Hi @Anonymous
You may use 'Fill down' feature with your condition column.
Regards,
Cherie
Thank you @v-cherch-msft this is the easiest solution, I didn't know there is a "fill" option right out of the box
Thank you @Ashish_Mathur your code works like charm!
ill be using the M code for this instance, but both give the expected result.
Regards,
Van
You are welcome.
Hi @Anonymous
You may use 'Fill down' feature with your condition column.
Regards,
Cherie
Hi,
In the Else portion, you want to pick up the value from the previous cell of the same column (in which you are writing that formula). Seems like a tough one. Can you anyways, share some data and also show the expected result. Would you be OK with a DAX solution?
Thank you for the response,
Here is screenshot
https://1drv.ms/u/s!Am-JUC7GnsuUtQncEobEaQpKzCmM
and sample in excel file
https://1drv.ms/x/s!Am-JUC7GnsuUtQhNQPqszL8u_cKb
Please let me know if it is possible to achive this using Power BI editor or DAX solution.
@parry2k Custom column = Get value from other column
else
Get Same column above value!
Thank you
Vans
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Coluxn1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Manual TAG", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Start([Coluxn1],5)="HdDte" then [Column6] else null), #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}), #"Added Custom1" = Table.AddColumn(#"Filled Down", "Custom.1", each [Manual TAG]=[Custom]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom.1"}) in #"Removed Columns"
Hope this helps.
@Anonymous in drop down "ABC123" select column option and that will do it for you.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |