Hi Ruth. Another fantastic tip. I wonder if when doing these in-column transformations can an IF statement refer to another column as well e.g. if _ = 101 and [Column1] = "x" then 999 else _ I get "We cannot apply field access to the type number" when trying. Maybe you already covered this.
Hi David & Ruth - I have exactly the same query - can you refer to a different column in the IF statement? This is such a great way to reduce the number of statements and would be just perfect if you could. Thanks again for all the help, Ruth
With a bit of digging, I found this stackoverflow.com/questions/31548135/power-query-transform-a-column-based-on-another-column but it is a bit complex - I wonder if you'd like to do a video on this Ruth... ;)
Excellent, one of the best Power Bi , Power Query and DAX channel i found, thanks Ruth for clearing very complex concepts of dax and power BI in such a nice way.. 1) Ruth, is there any possibility to start M Fridays (M language Power Query) same way as DAX Fridays. 2) Can you make detailed video on "lambda " function of excel. if it is possible Again thanks very much for providing such a excellent channel.
Hi Tariq, thanks for the feedback! Mondays are for M and Power BI videos, check them out and I dont do excel videos I am afraid, but the lambda function has been covered already by many, just search for it :)
This one is just what I need. One issue I have though is I have multiple each if statements to transform the one column. For example this is my statement is below. Curbal can you do multiple each if like I have below? = Table.TransformColumns(#"Duplicated Column", {{"TRAVELNBR", each if Text.Contains(_, "V") or each if Text.Contains(_, "Q") then Text.Middle(_, 0, 8) else _, type text}})
Excelente video Ruth!!! consulta sobre otro tema. Acabo de cargar la nueva versión de una Tabla desde Azure y obtengo el error "la secuencia no contiene ningún elemento coincidente" al actualizar de Power Query al Power BI, alguna idea sobre este error?? no he encontrado nada en internet, todo lo que hay está relacionado con otros temas. Agradezco tu respuesta !!!!! abrazo
hy, thanks for this. please help me with an issue like this : column with many lines - i want to find position and extract text, combination of certain letters "mo" & 1 number from 0-9, for example 1 line - didnt work something like tihis = Table.AddColumn(#"Changed Type", "EXTRACT", each Text.PositionOf([NAME],"mo" & {"0".."9"})) monk12 monique24 mo5266 mother21 cannot use mo , cannot find no, but i want to find position of mo5 and extract mo5266 - and so on to entire column thanks
@@CurbalEN I realize that is the syntax for that function, but why do you need two curly braces. I was under the impression that "curly brackets stood for rows" and "square brackets stood for columns." Do you know they use two curly brackets? I do enjoy your PQ videos. I did learn something here. The underscore means what is in the cell. Thanks for your hard work.
How may I remove a hyphen on left or right of a letter in a string, but do nother with a hyphen between numbers. Example below: 00-CM-00-12 00C-M-00-12 00CM-00-12 After transform steps, or in one step returns 00CM00-12 Thanks 🙏
May you plz tell me if there is any example for inserting text if row doesn't contain that text. Some rows have abc-1458 But some rows are without abc- I am not able to text.insert bcz then abc- gets doubled abc-abc-1458 Can anyone plz help
great video, love the deliberate mistake - its always good to see the mistakes so w can understand why it went wrong
It was not deliberate , but I kept in case it happened to you guys too ;)
Another reason why I kept the error in!
This was a FABULOUS video and has saved me a lot of heartache. Thank you very much!!
Nice to hear!!
this help me find out my answer, thank you
Glad :)
A perfect video as always.
I love that you kept the bug and fixing part in the video ahahhaa
😊 we are all in the same boat!
Thank you for all the effort you do to bring us always useful infos
Thank you so much! I am currently looking to extract the value. It is seven digits long. Hopefully you have a video on that.😊
Thank you very much Ruth!
Thank you for the quick tip video, it will come in handy. Great thumps up for you, Ruth
Received!!
Hi Ruth. Another fantastic tip. I wonder if when doing these in-column transformations can an IF statement refer to another column as well e.g. if _ = 101 and [Column1] = "x" then 999 else _ I get "We cannot apply field access to the type number" when trying. Maybe you already covered this.
Hi David & Ruth - I have exactly the same query - can you refer to a different column in the IF statement? This is such a great way to reduce the number of statements and would be just perfect if you could. Thanks again for all the help, Ruth
With a bit of digging, I found this stackoverflow.com/questions/31548135/power-query-transform-a-column-based-on-another-column but it is a bit complex - I wonder if you'd like to do a video on this Ruth... ;)
🤣Educational AND HILARIOUS!! Gracias!!
😂😂
Excellent, one of the best Power Bi , Power Query and DAX channel i found, thanks Ruth for clearing very complex concepts of dax and power BI in such a nice way..
1) Ruth, is there any possibility to start M Fridays (M language Power Query) same way as DAX Fridays.
2) Can you make detailed video on "lambda " function of excel.
if it is possible
Again thanks very much for providing such a excellent channel.
Hi Tariq, thanks for the feedback!
Mondays are for M and Power BI videos, check them out and I dont do excel videos I am afraid, but the lambda function has been covered already by many, just search for it :)
This one is just what I need. One issue I have though is I have multiple each if statements to transform the one column.
For example this is my statement is below. Curbal can you do multiple each if like I have below?
= Table.TransformColumns(#"Duplicated Column", {{"TRAVELNBR", each if Text.Contains(_, "V") or each if Text.Contains(_, "Q") then Text.Middle(_, 0, 8) else _, type text}})
Another cool trick .. loved it 😃❤
Thanks!
Fantastic❤
Excelente video Ruth!!! consulta sobre otro tema. Acabo de cargar la nueva versión de una Tabla desde Azure y obtengo el error "la secuencia no contiene ningún elemento coincidente" al actualizar de Power Query al Power BI, alguna idea sobre este error?? no he encontrado nada en internet, todo lo que hay está relacionado con otros temas. Agradezco tu respuesta !!!!! abrazo
Very helpful ruth
Glad to hear!
That error came up and you laughed , it was hilarious 😂
😂
Cool. And an enjoyable vid it was 😊
Mission accomplished then ;)
hy, thanks for this. please help me with an issue like this : column with many lines - i want to find position and extract text, combination of certain letters "mo" & 1 number from 0-9, for example 1 line - didnt work something like tihis
= Table.AddColumn(#"Changed Type", "EXTRACT", each Text.PositionOf([NAME],"mo" & {"0".."9"}))
monk12 monique24 mo5266 mother21
cannot use mo , cannot find no, but i want to find position of mo5 and extract mo5266 - and so on to entire column
thanks
A question from a newbie, why are there 2 curly brackets after "Source," and at the end after "type text" ?
It is the syntax needed for that function. The type text is to convert the column type to text on the fly.
@@CurbalEN I realize that is the syntax for that function, but why do you need two curly braces. I was under the impression that "curly brackets stood for rows" and "square brackets stood for columns." Do you know they use two curly brackets?
I do enjoy your PQ videos. I did learn something here. The underscore means what is in the cell.
Thanks for your hard work.
Hi Ruth, what is the concept of 'min' in this statement.
Just a specific text search in this example
@@NeBixAt Sorry, but I can't see the 'min' in the text, then it must return FALSE... or I'm lost.
Min is just some random text
Don't skip when watching the video, you will miss information, like where the min is.
I created a dashboard in power bi but it is showing as report how can i convert it to dashboard also please help me in creating alerts
i don't understand, if can't put just put a column name in it, then how can i do it if it depends on other columns?
How may I remove a hyphen on left or right of a letter in a string, but do nother with a hyphen between numbers. Example below:
00-CM-00-12
00C-M-00-12
00CM-00-12
After transform steps, or in one step returns
00CM00-12
Thanks 🙏
How would you do this if text contains
tell me more than 2 measure slicer
May you plz tell me if there is any example for inserting text if row doesn't contain that text.
Some rows have abc-1458
But some rows are without abc-
I am not able to text.insert bcz then abc- gets doubled abc-abc-1458
Can anyone plz help
Jajajaja eso fue divertido y Util