51 - Dealing with tables with varying column names and number of columns
Different methods to use Power Query to deal with column names that can change. I want to specifically NOT refer to a column with a hardcoded name e.g. I don't write "Sales", I write "the column name in the first column".
I also show how to handle different files having different amount of columns e.g. you only have a "shipping date"-column for sales that were shipped.
In the hard situation I also solve problems with a dataset having duplicate headers e.g. a row with headers, 30 rows of data, then a row with headers. SAP loves doing that...
00:00 Intro
00:23 Folder.Files gives a table with tables
00:50 Explaining the two situations
01:47 Create a parameter
02:16 Easy situation
02:36 Extract table (WRONG)
03:18 Table.ColumnNames
05:03 Turn "empty cells" into nulls
05:43 Calling the function
06:26 Hard situation
07:57 Extract the value from Column 1 Row 1
09:17 Wrong way to remove duplicate headers
09:42 The good method
09:50 Record.Field accesses a row's value
10:55 Select Each Record.Field
12:20 Always change the filename column-name
14:30 Append the tables together
Watch video 51 - Dealing with tables with varying column names and number of columns online without registration, duration hours minute second in high quality. This video was added by user WesleySon 27 November 2023, don't forget to share it with your friends and acquaintances, it has been viewed on our site 19 once and liked it 0 people.