Hi everyone, Welcome to Bsmart2gether. I am the wanted KT!
Please click subscribe and turn on the notification to avoid missing valuable videos.
Also, feel free to drop a comment or throw me any excel challenges, and I will show you
the best way to deal with them in future videos.
There is uncertainty at work, and we get inconsistent data structure. As a result, we spend a lot
of time cleaning the data rather than doing the actual work.
Today I will show you how to build a smart model to combine multiple files from different
file paths, promote headers row and remove empty columns.
The first section focuses on transformation and creating a custom function.
Firstly, let us import a workbook. I am importing the most unstructured file for today's lesson.
Here are the headers we want to promote to the column header row, but we have blank rows
and columns. Remember, every workbook are inconsistent structure. So, we need to know
A I to achieve it. I am kidding. Please keep your eyes open!
The first transformation step is to add an index column.
The headers are at row sixteen, and the index column will give the number fifteen. Let me
show you how we use the number fifteen in a second.
We need to add a custom column and name it "Create Record".
Type record dot to list, open parenthesis, underscore and close parenthesis. Then click
ok to continue. Click on the added column, row sixteen. You
will see the bottom information pane showing the list. Let's lift up the information pane
to see the full list. We want to add another layer to the existing
formula. Type list dot contains any, open parenthesis and adds a close parenthesis at
the end. The newly added formula searches through each
row for the keyword "Unit Cost" and returns either true or false. Click OK to continue.
Only row sixteen returns true, which is what we want.
Now filter for "True". Add this code, and you will get the number
of rows to skip to get the headers before we promote them to the headers' row.
We have another two formulas to add to the existing one. We need to go back to the "Added
Index" step and copy the highlighted reference code before we write to add the two formulas.
Now, go back to the last step and add table dot skip, open parenthesis, paste the copied
code and add a close parenthesis at the end of the formula.
This formula will dynamically skip rows and bring the headers to row one.
Add table dot promote headers, open parenthesis and add a close parenthesis at the end of
the formula. The last transformation step is to remove
empty columns. The formula can be a bit complex and required to combine them together to have
it works. Click on the f x to create a custom step.
Please replace the default reference code with this formula.
Let's delve into a bit about the formula. The logic is we want to select all other columns
except these two empty columns. We use this formula to get data from each
of the columns from the previous step. We then applied this formula, searching through
each row that is not equal to null and returning a list of columns that is not empty. We then
use this formula to get the complete column name list and use this formula to select only
the column names that appear on the list from the matches formula. Now we have the list
of column names that we want, and we can use this formula to select only the columns on
the list. Hit enter, and the empty columns are now removed.
Let's rename the steps. The last step is to turn this query into a
function. Go to the advance editor. Enter to insert a row before "Let" and enter
this code. Then, we need to replace the current folder path, file name and worksheet name
with the added code. Click done, then rename the query to f x transformation.
We just created a custom function. Let us create a parameter input table then
I will show you how to apply the function. Let's create a parameter input table with
three columns: Folder path, Filename and worksheet. Then, import the table into Power Query.
Now we can apply the custom function. Now we can apply the custom function. Enter
the f x transformation formula as per the call out box. Click ok to continue.
Let's click on each table and check out the result from the preview pane. Awesome!! The
result looks great!! We want to combine those files now. Right-click
on the custom column header and select drill down.
All we need to do is to add the table dot combine formula. Ta-dah!! All the files are
now combined perfectly. There is a downside with the text dot combine
formula. You lost the Filename. I want to show how to resolve it.
Go back to the added custom step and add another custom column.
What we want to do is to add the filename to each of the tables.
Click Ok to continue. Click on the first table to check the result. The filename is now added
to the tables. Recap, we wrote the table dot add column is
to add a column in each of the tables. Now go to the custom step, and we need to
update the formula from custom to custom dot one. Then, take a quick check of the result.
All good! We can load it to excel now. Thank you for watching, and I hope you find
this video helpful. Please don't forget to click like if you like the video.