Replace Values and Column Data Types with Power BI (Power Query)

[@jensimonson3754]

Good stuff. Thanks for sharing! One thing to add - You can change data types on multiple columns easier than that - Just select all the columns you want to change, using Ctrl and right-click and choose "Change Type"

[@alexeimatcov7928]

Very useful piece of information. Thanks a lot!

[@keiladasilva8523]

Thank you!

[@cokiebee9318]

This was very helpful. One question- once you make the update does it change automatically every time you update the query? Or do I have to do this step each time?

[@ThePowerUser]

Hey! kind of - it depends. If you add new columns then you'll need to go back to your query and set the data types for each of the columns, but otherwise you wouldn't have to do anything else.

[@_Areknames_]

at the end you speak about the command to replace values as per column type, you speak of an"Upsdrive data type"..but i cannot find such command..can you be more clear? thank you for your videos (great)

[@ThePowerUser]

Hey! Check out this article about data types www.poweredsolutions.co/2018/03/12/data-types-data-conversion-ascribed-data-types-power-query-power-bi/

[@19859ahmed]

Nice video 👍, but why you didn't drag down the change type step?

[@ThePowerUser]

Hey! Thanks for the kind comment. Hope you enjoyed it. Regarding the question--> Flexibility! Imagine one of the following possibilities: - One of the column names changed - A new column was created that was not taken in consideration in the Changed type step - Perhaps another changed type was applied before doing the 'Replace Values' If any of them happened, which more often than not do, dragging the 'Change type' would not only not work, but it would also have a negative impact in your solution as some steps might rely on a specific data type and going into the "any" data type would be a bad idea. tl:dr Dragging the 'Change type' step would only work in certain scenarios, whilst the method shown in this video would work in almost every scenario.

[@vida1719]

Great video! Do you know if it’s possible to add today’s date instead of null value, if the column contains dates and some rows are nulls? Would today’s date update next day/time with refresh?

[@ThePowerUser]

You could use this function: msdn.microsoft.com/en-us/library/mt253492.aspx and instead of "each [Average]" you'd just use that Function and wrap it around a "Date.From" so it only shows the Date part

[@papachoudhary5482]

Thanks

[@ioanniskiokpas4603]

Greetings! when you typed each [Average] at 02:00, is it possible to type something like AnotherQuery[Column] and read values from another query's column and do the replacements?

[@ThePowerUser]

Hey! In short, yes. You can read the data from another query and inject it into this other query. How? that depends. Maybe what you're looking for is a Merge operation instead of a replace operation?

[@ioanniskiokpas4603]

@@ThePowerUser thanx for the quick reply! It is actually replace that I am looking for and not merge. Because it would be Text.Contains for my query so that I will partially replace text with values from a replacement table. I have tried it with something like = Table.ReplaceValue(#"Renamed Columns", each Replacements[original],each(Replacements[replacement],Replacer.ReplaceText,{"Activity"})"" but i dont think I am getting the syntax right!

[@ThePowerUser]

​@@ioanniskiokpas4603 if you're using Power BI Desktop, then you can use a new function called Text.ReplaceAll I highly recommend that you post your question and full scenario on the official Power Query forum: social.technet.microsoft.com/Forums/en-US/home?forum=powerquery

[@cantcatchme8368]

is it possible to replace values in one table by using other table values

[@ThePowerUser]

www.poweredsolutions.co/2019/07/01/recursive-functions-in-power-bi-power-query/ this might help

[@Saur]

Your videos are extremely helpful, thank you so much for sharing.

[@ThePowerUser]

hey! thank you for the kind words. Hope you enjoyed the video :)

[@erickchicas9808]

Hello , i´m trying to follow your video my column to get information when empty is called ship to party and needs to changed null from columns case e2e... my formula its the following Table.ReplaceValue(Source,null, each [Ship To Party Description], Replacer.ReplaceValue,{"Case E2E Name"}) both columns are text .... once i enter this formula i get all my columns names in each row but not the nulls to have a name

[@ThePowerUser]

Hola Erick! The formula should work, but it also depends on how your data looks like. I highly recommend that you post your scenario with sample data on a forum. I have one, but its in Spanish. You can try the official Power Query forum which is in English from here: social.technet.microsoft.com/Forums/en-US/home?forum=powerquery

[@MuhammetMustafaAslan]

Love what you are doing here :)

[@wynhopkins4023]

Really helpful, thanks

[@adityakalra7383]

This is useful 😁