APQ08: Power Query List Functions - Bulk Replace values in a table using a helper table

[@alexrosen8762]

Great session! Thanks a lot

[@AAAExcel]

My pleasure

[@zahoorsarbandi2982]

Very verry useful tricks. in daily working with power query these tricks are too much needed. Thanks for such a nice content which helps a lot.

[@AAAExcel]

You’re welcome dear

[@roywilson9580]

Another interesting video, thanks for uploading.

[@AAAExcel]

My pleasure Roy

[@raissabenoccithibes3768]

Amazing class! Useful tips I was looking for!

[@AAAExcel]

Happy that you found it, thank you 🙏
Please subscribe to my new channel dedicated for English content
youtube.com/@aaaexcelenglish

[@paolomartinello76]

Are you kinda genius maybe? Many compliments for this video!

[@AAAExcel]

Thank you so much for you comment dear!

[@jeromeastier462]

Excellent! Exactly what i was looking for, Thanx!!

[@AAAExcel]

Great to hear!

[@aatsw]

Very nicely done, learned something new today. Thanks for sharing. Merry Christmas & Happy New Year!

[@AAAExcel]

Thank you son much, M. Xmas and happy new year to you too

[@malchicken]

Very neat trick, thank you :) 🙏🏽.

[@AAAExcel]

My pleasure, thank you 🙏

[@syedaneesdurez7197]

Very helpful Video to do our daily tasks. Mashallah

[@AAAExcel]

Thank you 🙏

[@antonysovy5887]

this is amazing as always

[@AAAExcel]

Thank you Antony 🙏

[@bagnon]

I also tried this with unpivot the columns, do the List.ReplaceMatchingItems step on the one Values column, Pivot the table back and then Table.ExpandListColumn.

[@AAAExcel]

Nice trick, this will work too, thank you 🙏

[@kishorpanara4191]

Very very useful video

[@AAAExcel]

Thank you 🙏

[@mirrrvelll5164]

Good one! Record more PQ videos =)

[@AAAExcel]

Will do my best - thank you 🙏

[@kailash680]

10:45 This will give a Formula.Firewall Error. Not when writing it, but after reopen the file, even with your final exercise file. But the final result is not affected.

[@AAAExcel]

I believe this error is nothing to do with the function itself, I will recheck it from my side anyway

[@dieudonnepare9295]

Very Good !

[@AAAExcel]

Thanks!

[@manjeetmarothiya1519]

Hi Thanks for making video on List M code, thanks for explaining this Topic in very Deep and easly.
I request you Please make a Video on How to Import a Zip or Gzip CSV File in Power query with the Help of Import zip file M - code.
Thanks,

[@AAAExcel]

Thank you for your comment, I will consider in future plans :)

[@lpenatti]

It worked very well! Thanks for sharing. However, in the last step, all column types are changed to general (acb123). Is there a way to keep original column types, like you did for column names?

[@ShaalanHalahleh]

How can I change the values in each column with different helper tables?

[@jiky4296]

when i am giving this command List.ReplaceMatchingItems([Column1],oldnew) i am getting list of errors instead of list of list

[@jiky4296]

I am doing exactly same steps still i am geeting error when replacing

[@LawsonAaron-h6d]

Goldner Grove

[@dhaval1489]

Sorry this way too complicated, I have simpler solution first unpivot all the columns expect first 2 columns, then merge the two table, then replace null value with some place holder values then use ReplaceValue to get value from other column, then delete the 2nd column the pivot back and done, in this case the number of columns in first table and number of replacement in second table don't matter at all

[@AAAExcel]

nice idea, if are not confortable with functions, unpivot, merge and pivot is a good solution too

[@harreka]

@@AAAExcel Hi, I've tried this several times because I need to do this function, but i'm getting the following error: Unexpected error: An item with the same key has already been added.
Details:
Microsoft.Mashup.Evaluator.Interface.ErrorException: An item with the same key has already been added. ---> System.ArgumentException: An item with the same key has already been added. ---> System.ArgumentException: An item with the same key has already been added.
at System.ThrowHelper.ThrowArgumentException(ExceptionResource resource)
at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)
at Microsoft.Mashup.Engine1.Runtime.Library.List.ReplaceMatchingItemsFunctionValue.ReplaceAllListValue.GetEnumerator()
at Microsoft.Mashup.Engine1.Library.Table.TableModule.Table.FromColumnsFunctionValue.FromColumnsTableValue.GetEnumerator()
at Microsoft.Mashup.Engine1.Language.Query.SkipTakeEnumerable.GetEnumerator()
at Microsoft.Mashup.Engine1.Language.Query.QueryTableValue.GetEnumerator()
at Microsoft.Mashup.Engine1.Runtime.TableValue.Microsoft.Mashup.Engine.Interface.ITableValue.GetEnumerator()
at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.ValuePreviewValueSource.TableValuePreviewValueSource.SerializeRows(Int32 count)
at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.ValuePreviewValueSource.TableValuePreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Evaluator.Interface.TracingPreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.c__DisplayClass0_0.b__0()
at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(String entryName, IEngineHost engineHost, IMessageChannel channel, Action action)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.EvaluationHost.c__DisplayClass17_0.b__1()
at Microsoft.Mashup.Common.SafeExceptions.IgnoreSafeExceptions(IEngineHost host, IHostTrace trace, Action action)
at Microsoft.Mashup.Evaluator.EvaluationHost.TryReportException(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Exception exception)
at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(String entryName, IEngineHost engineHost, IMessageChannel channel, Action action)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.c__DisplayClass12_1`1.b__0()
at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetResult[T](IMessageChannel channel, BeginGetResultMessage message, Action`1 action)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetPreviewValueSource(IMessageChannel channel, BeginGetPreviewValueSourceMessage message)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.OnMessageWithUnknownChannel(IMessageChannel baseChannel, MessageWithUnknownChannel messageWithUnknownChannel)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.EvaluationHost.Run()
at Microsoft.Mashup.Container.EvaluationContainerMain.Run(Object args)
at Microsoft.Mashup.Evaluator.SafeThread2.c__DisplayClass9_0.b__0(Object o)
at Microsoft.Mashup.Container.EvaluationContainerMain.SafeRun(String[] args)
at Microsoft.Mashup.Container.BootstrapAppDomainManager.Execute(String[] argv)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.EvaluationHost.OnException(IEngineHost engineHost, IMessageChannel channel, ExceptionMessage message)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.Interface.IMessageChannelExtensions.WaitFor[T](IMessageChannel channel)
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing)
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Evaluator.Interface.TracingPreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Host.Document.Analysis.PackageDocumentAnalysisInfo.PackagePartitionAnalysisInfo.SetPreviewValue(EvaluationResult2`1 result, Func`1 getStaleSince, Func`1 getSampled)