Combine tables with Different Column Names

M_K · February 9, 2021, 7:37am

Hi

I have a few tables that I need to combine.

Below is an example of the input tables (source files - and these are in a folder),

The expected result after appending the tables should be,

Column 1 - Product name (i.e. Product A / B / C , etc)
Column 2 - Account name (i.e. revenue, exp 1, exp 2, exp 3, etc)
Column 3 - month
Column 4 - values

Could I please get some help on how I could achieve this?

Thanks
Manoj

BrianJ · February 9, 2021, 7:41am

@M_K,

Interesting PQ problem. Can you please post some sample files that we can test on?

Thanks.

Brian

AnshP · February 9, 2021, 7:56am

Hi Manoj,

this is my sample as per your requirement

and this is my result.

Manoj.pbix (20.6 KB)
.pbix is also attached.

all you need to do is make the first row as header, let the Product name (a/b or c) and the months be the header, keeping the first columns as it is, unpivot other columns, once this is done, for each file add a col from example and then re order. Do this for all the separate files and once done you can append them.

hope this helps
Regards
Ansh

AnshP · February 9, 2021, 7:58am

@M_K,

You can actually go even further and automate this. I can do this for you tomorrow as it is midnight here so I am off, but the example that i provided should come in handy. I will check back tomorrow, if you still need assistance, I will be more than happy to help you .

Regards

M_K · February 9, 2021, 8:22am

@BrianJ

Here is a sample file.
Please note, I have set up the files as worksheets within the workbook attached - in reality, each worksheet sits inside different Excel Files.

Here is the background,
The Excel files are saved in a folder.
Each Excel file has more than one worksheet - and these are named consistently.
When loading the data in PQ - I am using combine and transform data, and selecting the worksheet I need.

Thanks
Manoj

SampleFile.xlsx (14.9 KB)

M_K · February 9, 2021, 8:26am

@AnshP

I’d prefer to combine and load directly from a folder - the files that are saved in the folder may change in the future.

Thanks
Manoj

AnshP · February 9, 2021, 5:31pm

Hi Manoj,

Please find the .pbix attached. To make this truly dynamic, create a function that will get invoked each and every time you specify the workbook name. In this case it is Product_A, Product_B or Product_C.

Once you do the transformation for one file then create a function as it will ensure that all those transformations take place for other files.

A few things to remember, since the column header will change, it is my recommendation to either use a empty table header as the schema (just like what you would use during Azure Data Factory dataflows or pipelines). Or if you are unable to have a schema then use generic column header names such as column1 etc.
I have incorporated one such approach using functions in the .pbix. My approach was two pronged, first using the data file, i separated Product name and the respective dates. Second half was focused on the getting the account details and values. to perform the join I used the column1 etc. (attributes) though it would be ideal if you could have an index associated with it. (Numeric index performs faster and are less prone to mistakes, in my opinion)

Once I was able to get all this, invoked the function for Product_B and it worked like charm. If you come across any errors such as could not find column x in the table, that means you will need to tweak the query transformations as per the table schema.

I hope this will help you and have a look at the EDNA resource about the PQ transformations.

Regards
Manoj.pbix (33.3 KB)

BrianJ · February 9, 2021, 5:44pm

@AnshP,

Great solution! Would you mind posting the xlsx data file as well?

Thanks for the fantastic support you’re providing on the forum lately.

Brian

AnshP · February 9, 2021, 6:51pm

Suue @BrianJ

The data file (excel data sheets) was provided by manoj and i used the sames one. Here are both the files once again.

data file SampleFile.xlsx (14.9 KB)

.pbix Manoj.pbix (33.3 KB)

I am glad to be of any assistance to EDNA and everyone associated with it.

Regards
A

M_K · February 10, 2021, 8:33am

Thanks @AnshP for taking the time to work through this.
However, I am unable to follow your steps.

In the meantime, I’ve managed to work this out a different way.
I’ve done the ETL in Excel - file attached.

CombinedData.xlsx (26.7 KB)

The Data folder has 3 files, Product_A, Product_B and Product_C. These files have a worksheet each named Product

Here are the steps,
Data tab - Get Data > From Files > Folder
Select folder path (where the files are saved) - click OK
Select Combine and Transform Data
In the ‘Combine Files’ dialogue box, Sample File = First file, Select Product worksheet and click OK
When the PQ Editor opens go to ‘Transform Sample File’ query in the Queries pane. This is where I’ve done the transformations.

Extract from Advanced Query Editor before transformations,
let
Source = Excel.Workbook(Parameter1, null, true),
Product_Sheet = Source{[Item=“Product”,Kind=“Sheet”]}[Data]
in
Product_Sheet

Transformations,

Promote Headers - use 1st row as headers
If the ‘Changed Type’ step is added by default - delete this
Select the 1st column and ‘Unpivot Other Columns’
The unpivot step will save the name of the 1st column in the query,
= Table.UnpivotOtherColumns(#“Promoted Headers”, {“Product A”}, “Attribute”, “Value”)
Replace the above with,
= Table.UnpivotOtherColumns(#“Promoted Headers”, {List.First (Table.ColumnNames (#“Promoted Headers”))}, “Attribute”, “Value”)
Rename the first column - AccountName
The rename step will save the name of the 1st column in the query,
= Table.RenameColumns(#“Unpivoted Other Columns”,{{“Product A”, “AccountName”}})
Replace the above formula to make this dynamic, i.e. rename the 1st column of the table to AccountName,
= Table.RenameColumns(#“Unpivoted Other Columns”,{{Table.ColumnNames (#“Unpivoted Other Columns”) {0}, “AccountName”}})

Go to the ‘Data’ query (combined query)

Select the 1st column - go to Transform > Extract > Text Before Delimiter, type in .xls
This gives me the product name
Rename 1st column to Product, 3rd column to Month
Change type

Thanks
Manoj

AnshP · February 10, 2021, 2:03pm

Hi @M_K,

I am glad that worked out for you. There are many ways to work out same problem in PQ. The steps that you followed here can also be done in PQ of Power BI as they are the same. You can perform the same ETL there. See the first file attached. It has the similar if not exactly same steps.

Regards