I've watched many related videos but the way you explain is way more clear and simple. Thank you so so much
@AccessAnalytic
6 ай бұрын
No worries, thanks for taking the time to leave a kind comment
@dirkstaszak4838
2 ай бұрын
what would be the short cut for a German Excel Verison?
@AccessAnalytic
2 ай бұрын
I don’t know sorry
@zahoorsarbandi2982
Жыл бұрын
very nice lecture specially: Shift+Down Arrow selects the step name, then Shift+9... wonderful
@AccessAnalytic
Жыл бұрын
Thank you Zahoor
@radu_sirbu
Жыл бұрын
That's a lifesaver! It's exactly what I need for my current task at work.
@AccessAnalytic
Жыл бұрын
Excellent
@paulsingleton6071
8 ай бұрын
Hi Wyn, great video, really useful. Thank you. 👍👏
@AccessAnalytic
8 ай бұрын
You’re welcome
@sarahciez2434
Ай бұрын
This was the clearest explanation I could find, thank you so much!
@AccessAnalytic
Ай бұрын
You're welcome, thanks for taking the time to let me know 😀
@GeertDelmulle
Жыл бұрын
Hi Wyn, Here's a one step solution that gets straight to the point. In your rename step use this formula: = Table.TransformColumnNames(Source, each Text.AfterDelimiter(_,"_")) and be done with it. 🙂 You're welcome.
@AccessAnalytic
Жыл бұрын
Nice Geert
@AccessAnalytic
Жыл бұрын
I added it to the description section
@txreal2
Жыл бұрын
Where is the time stamp for this step? Thanks Geert
@AccessAnalytic
Жыл бұрын
@@txreal21:21
@marcelakarinalopezortiz3373
Жыл бұрын
@@AccessAnalytic do you have a similar solution when you want to add a prefix/suffix?
@God......4579
3 ай бұрын
Very informative! Love the clear explanation. 🙏🏿
@AccessAnalytic
3 ай бұрын
I appreciate you taking the time to let me know you found it useful
@rodolfoflores4387
26 күн бұрын
Excellent technique, as always.
@AccessAnalytic
25 күн бұрын
Cheers 😀
@danishnawaz3651
8 ай бұрын
Sir, plz make video for below. If 1 table has 9 column, 2 table has 10 and 3 table has 8 . Then how can we make equal all column for all table.. like we want to insert dummy column for missing column in tables and make equal like 10 column for each table.
@AccessAnalytic
8 ай бұрын
That should happen automatically. Tables don’t need the same number of columns
@alexbarbucristi
Жыл бұрын
Hi Wyn, My approach was: Use Headers as first Row, Transpose, Apply transformations needed to column 1 which contains the names of the original columns. Transpose, Use first row was headers
@AccessAnalytic
Жыл бұрын
Nice technique with a small data set. Not sure what would happen with 500,000+ rows
@alexbarbucristi
Жыл бұрын
@@AccessAnalytic Thanks for the heads up, will stop using it
@Rodrigo-j6o7x
3 ай бұрын
Nice video and tips... "shift + 9", I'll only add that the concept behind is: mark the text and "open whatever bracket you need", then you'll have the seleted text with both ( ), { }, [ ].
@AccessAnalytic
3 ай бұрын
Cheers
@BobXu-i7v
3 ай бұрын
awesome!
@AccessAnalytic
3 ай бұрын
Cheers
@FredericLEGUEN-Excel
Жыл бұрын
Nice trick. And I love Shit + 9 😉
@AccessAnalytic
Жыл бұрын
Cheers Frédéric
@GrainneDuggan_Excel
Жыл бұрын
Fantastic timesaver and frustration remover! Wish I knew Shift+9 sooner! Thanks Wyn!!!!!! 👏👏👏👏👏👏
@mcnater
Жыл бұрын
@@GrainneDuggan_Excel what does Shift + 9 do?
@GrainneDuggan_Excel
Жыл бұрын
@@mcnater watch at about 2:35 min when you type a function name then Shift+Down Arrow selects the step name, then Shift+9 puts the (before and )after!!
@AccessAnalytic
Жыл бұрын
Thanks Grainne, i wish I knew it sooner too 😆
@kalyanreddy6260
5 ай бұрын
Still getting error. I jave two tables 2022sales, 2023 sales. In first file i have columns carname, year, number of units sold, country. In secnd file i have diffent name number of units as (units) and county as (place) . Ihave created maping tabme exactly and turn it to rows and using in my sample file query. Now the second still shows error not displaying records.a red line across all headers. All date types are checked though
@AccessAnalytic
5 ай бұрын
If you’re running this on a folder of files then you might want to add MissingField.Ignore Something like Table.RenameColumns(_,{{"Product","Item"}}, MissingField.Ignore) This should handle the fact that the columns you’re renaming don’t exist on one file
@Mohammad-rz7oj
4 ай бұрын
I have spreadsheet with new and old columns names. I see there are lot of steps in video. Could you please let me know how to rename the columns in the table using the spreadsheet. I tried few steps what you showed in the video. getting error like cannot convert a value of type table to type list.
@Mohammad-rz7oj
4 ай бұрын
Sorry it is working now. Just small doubt, could you please let me know whether we can get rid of spreadsheet once we rename the fields so that we dont have any dependency on the spreadsheet
@AccessAnalytic
4 ай бұрын
@Mohammad-rz7oj the Power Query code pulls the data from a source and loads it to a destination. If you never to update or refresh the query then yes you can delete the source.
@Mohammad-rz7oj
4 ай бұрын
@@AccessAnalytic Thanks for the reply. All these calculations are not working for directquery. What i did instead is wrote a calculation in excel to concatenate old and new column names and updated those in M Lang in Powerquery editor.
@ajayrathod7777
2 ай бұрын
Will this approach work if i unpivot data? And download link is not working
@AccessAnalytic
2 ай бұрын
You can’t use button clicks on user interface if connection to data isn’t working. Unpivotting is a good technique to avoid referring to columns
@mcwahaab
Жыл бұрын
Awsome! I have seen other long approaches but this is the one. Thanks a lot Wyn
@AccessAnalytic
Жыл бұрын
You’re welcome
@MaiMai01-w4k
7 ай бұрын
Excellent
@felipesignorellireis7839
Жыл бұрын
Love it
@AccessAnalytic
Жыл бұрын
Thanks
@AndrewCragg-y8v
2 ай бұрын
I keep getting We expected a RenameOperations value error.... :(
@AccessAnalytic
2 ай бұрын
Not sure sorry.
@RahulForPWBI
4 ай бұрын
Easy Peasy; Thank You so much!
@AccessAnalytic
4 ай бұрын
You’re welcome.
@exceltina
7 ай бұрын
When I refresh query once i get a new file, will it read the data?
@AccessAnalytic
7 ай бұрын
Yes it will
@rudi-gruber
Жыл бұрын
very helpful, thanks
@AccessAnalytic
Жыл бұрын
You're welcome 😀
@josecarlosconejo5724
Жыл бұрын
Very Useful 😮 Impressive 👏
@AccessAnalytic
Жыл бұрын
Cheers
@dbalkin777
Жыл бұрын
Great video! Related to your “clicks” comment, how much M do you write in your work now? I feel like I’ve reached a place with PQ that that is what I should focus on now, but seldom feel like I have a use case that can’t be solved with the “clicks.”
@AccessAnalytic
Жыл бұрын
Thanks, probably 10% on average is hand written code
@erdiaz39
Жыл бұрын
Awesome tips!!!
@AccessAnalytic
Жыл бұрын
Cheers 😀
@bembeyerenkhoma
Жыл бұрын
I have a question based on the power query how can I get in touch with you? Do you have an email address or any other means of communication apart from this platform?
@AccessAnalytic
Жыл бұрын
Check out the description for my links, also for most questions I point folks to techcommunity.microsoft.com/t5/microsoft-excel/ct-p/Excel_Cat?WT.mc_id=M365-MVP-5002589 And community.powerbi.com/t5/Power-Query/bd-p/power-bi-services
@telclissonperes
Жыл бұрын
Thank you so much!!!!
@AccessAnalytic
Жыл бұрын
No worries
@PeterWalker
Жыл бұрын
How would you handle the error that the field name already exists in the record? I have that error and although I can tell the rename function how to handle missing values there isn't anything tat will say this is what to do when you have an existing name.
@AccessAnalytic
Жыл бұрын
Not sure, maybe some sort of pre check on the renaming table and add a 2 if text already exists if List.Contains( Table.ColumnNames(OriginalTable), [NewName] ) then [NewName] & "2" else [NewName]
@PeterWalker
Жыл бұрын
@@AccessAnalytic sounds like a course of action. Thanks for the response!
Пікірлер: 74