Reference items by position in Power Query | Transform reports into data | Excel Off The Grid

Показать описание

★ Want to automate Excel? Check out our training academy ★

★ About this video ★
This video demonstrates two ways of reference items by position in Power Query. The technique is used to change a Trial Balance Report into data.

0:00 Introduction
0:19 The Scenario
1:00 Name of previous step
1:32 Referencing value by position
2:25 Create a date
3:19 Removing top rows
3:38 Promote headers
4:10 Rename column by position
5:20 Remaining clean up
5:20 View the code
6:05 Conclusion

★ Download 30 most useful Excel VBA Macros ebook for FREE ★

★ Where to find Excel Off The Grid ★

#MsExcel #PowerQuery

Excel Off The Grid

Рекомендации по теме

Комментарии

This was a brilliant simple solution for the renaming by position, life saver!!!

I'm with Ismael Kourouma - laughing here ! as you have also helped me out ! Thank You for sharing.

paulstokes

Your content is consistently excellent. Many thanks.

houstonsam

Thanks Mark. This is easiest of the methods I have seen so far. Keep up the good work.

hassanjatta

This is a great video Mark. I had this issue some month ago about the hard coded date and could not solve at all. When added the new data, I got the error. i am just laughing here as this video is definitly helping me to sorted out that issue. Many thanks :)

ismaelkourouma

Great tips! I was just struggling about the same problem some days ago.

gabrieleranucci

Thanks a lot, That Great Tip, solved many of my issues

vsrinivasan

Thanks Mark, can't help but laugh when I saw this...faced this challenge, and drove me mad for a whole week before I figured it out, coincidentally with one of your other videos. But happy to learn this nonetheless. Many thanks! 🙂

KSyndicate

Like to see the step bringing in next month.

nuqwestr

wow what a timing of this video. i was exactly looking a solution to this!!

sasikr

I think if you would move with adding custom dates column after promoting headers but still referring to step no 2, it would not require changing column name.

ap.pa

Hi Mark what if you have different dates

thababozulu

What if I want change more columns in 1 step?
I Have this:
#"Changed Type1" = Columns", {{Table.ColumnNames(#"Removed Columns"){1}, Int64.Type}})
and this works fine
I like change 1-5
Thanks

erikeringaad

Reference items by position in Power Query | Transform reports into data | Excel Off The Grid

Reference items by position in Power Query | Transform reports into data | Excel Off The Grid

MS Excel - Cell Reference

Excel Cell Reference: Absolute, Relative or Mixed?

Excel Magic: Master Lookup & Reference Functions for Top-Notch Position and Grade Calculations!

3D scanning tip: Find the best position for your reference points

Internal Reference Generator - Adjust Internal Reference Parameters Position Odoo

Carbohydrates Part 1: Simple Sugars and Fischer Projections

How to position and draw reference lines using the 3-4-5 rule and a chalk line: EYS Short

Percentiles (1.7)

Find Multiple Match Results in Excel (Easier Solution For ALL Excel versions)

APA 7th Edition: The Basics of APA In-text Citations | Scribbr 🎓

Excel OFFSET Function for Dynamic Calculations - Explained in Simple Steps

Three EASY Ways to Find and Remove Duplicates in Excel

Index Match Advanced: 3 Most Effective Formulas for Multiple Criteria

REINDEER ITEM + NEW SECRETS WINTER UPDATE

Inventory Basics - Whiteboard Wednesday

Context Clues | English For Kids | Mind Blooming

Kinematics Part 3: Projectile Motion

Python Tutorial for Beginners 5: Dictionaries - Working with Key-Value Pairs

HVAC Training Basics for New Technicians and Students! Refrigeration Cycle!

5 Excel INTERVIEW Questions You NEED to Get RIGHT

Change of basis | Chapter 13, Essence of linear algebra

AULA 08 FANUC - Reference Position

Le Chatelier's Principle