Merge data of same column names into same column

[Anonymous]

I have several Excel files that will be generated weekly and produce different weekly forecast. The number of week forecasts varies for each file. Below are the examples:

Week 1:

Week 2:

Week 3:

I wish to transform the dataset to below image in Power Bi Power query:

-The new weeks generated for each file will be added to the right of previous file.

-The categories 'Condition' and 'Region' are sorted into same columns.

-In the future, the newly generate files (File 4, File 5 and so on) can be combined automatically into subsequent rows through the query.

Basically, I wish to compare the forecast difference on the same date of different files for each category. 

Any idea to do this? I am new in Power Bi. Really appreaciate any help given. 

Thank you.

[KT_Bsmart2gethe]

Hi Sunshiner,

The issues come from each file's column names being column1,2,3,.........

Promoteheader need to be done as part of the transformation.

Below is the code:

let
//Import from folder
Source = Folder.Files("C:\Users\cktan\Documents\PQ Solutions"),

//Filtered out hidden files
#"Filtered Rows" = Table.SelectRows(Source, each not Text.Contains([Name], "~")),

//Get table/data from each workbook
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "GetTbl", each Excel.Workbook([Content])),
#"Expanded GetTbl1" = Table.ExpandTableColumn(#"Added Custom", "GetTbl", {"Data"}, {"Data"}),

//Promote Headers before expand
PromoteHdrs = Table.AddColumn(#"Expanded GetTbl1", "PromoteHdrs", each Table.PromoteHeaders([Data], [PromoteAllScalars=true])),

//Get Column Names from each workbook
GetColNames = Table.AddColumn(PromoteHdrs, "GetColumnNames", each Table.ColumnNames([PromoteHdrs])),

//Remove duplication of ColumnNames
ColumnNamesList = List.Distinct(Table.SelectColumns(Table.ExpandListColumn(GetColNames, "GetColumnNames"),"GetColumnNames")[GetColumnNames]),

//Removed other columns before expand
#"Removed Other Columns" = Table.SelectColumns(GetColNames,{"PromoteHdrs"}),

//Dynamic Column Name list for expanding
#"Expanded PromoteHdrs" = Table.ExpandTableColumn(#"Removed Other Columns", "PromoteHdrs", ColumnNamesList)
in
#"Expanded PromoteHdrs"

I hope this help.

Regards

KT

[Vijay_A_Verma]

Solution file is uploaded to https://1drv.ms/x/s!Akd5y6ruJhvhuVNvPkBvziatFSaT?e=4tiK2i 

Replace path C:\Test to your path. This will merge files dynamically as per your need even though your date column names vary every week.  

You can see the queries from Data tab - Queries and connections

[Anonymous]

It shows error after the week 2 and week 3 files are added into the folder 

[Vijay_A_Verma]

Click on Error and post the message here. 

[v-yanjiang-msft]

Hi @Vijay_A_Verma ,

I get this error in the same step "Invoke Custom Function1".

Best Regards,
Community Support Team _ kalyj

[Vijay_A_Verma]

Please post the code of this transform file here.

[v-yanjiang-msft]

Hi @Vijay_A_Verma ,

I attach my sample data below, I just followed the steps you introduced and got the error, you can have a try.

Best Regards,
Community Support Team _ kalyj

[Vijay_A_Verma]

The method which I have given is for combining files from a folder. 

The method is different if you want to merge the sheets from a file.

1. If you want to merge sheets from a file, Get Data - From File - From Excelworkbook

2. Another method is that convert all tables given into structured tables (by CTRL+T), open a blank query from PQ and then type following and enter

= Excel.CurrentWorkbook()

The advantage of method 1 is that you need not convert tables into structured table first. 

[Anonymous]

pardon, the link redirects me to a blank onedrive excel file 

[Vijay_A_Verma]

Looks like you missed reading this - You can see the queries from Data tab - Queries and connections

You can double click on Test

[BA_Pete]

Hi @Anonymous ,

Providing all of the columns in each file have the same data type, then you should just be able to append all the tables in Power Query.

The most dynamic way to do this would be to keep all of your forecast files in one folder together, then use either the Folder connector, or the SharePoint folder connector to pick up all files in the folder and append them at refresh.

https://docs.microsoft.com/en-us/power-query/connectors/folder 

https://docs.microsoft.com/en-us/power-query/connectors/sharepointfolder 

Pete

[Anonymous]

yes, however, the columns of subsequent files are not sorted to the same column as their positions are different. 

[BA_Pete]

Hi @Anonymous ,

Have you tried it?

As far as I'm aware, Power Query append works differently to SQL UNION in that it matches columns to append based on the column names, not on the order in which they feature.

*EDIT*

I've just tested, and given the following source tables:

// aTable
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIFYjOlWB0IzwKIzcG8JJC4JVgIxjUBYiOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [cat = _t, #"01/01/2022" = _t, #"01/02/2022" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"cat", type text}, {"01/01/2022", Int64.Type}, {"01/02/2022", Int64.Type}})
in
    #"Changed Type"

// bTable
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIFYjOlWB0IzwKIzcG8ZJC4JVgIxjUBYiOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [cat = _t, #"01/02/2022" = _t, #"01/03/2022" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"01/02/2022", Int64.Type}, {"01/03/2022", Int64.Type}})
in
    #"Changed Type"

// cTable
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIFYjOlWB0IzwKIzcG8JJC4JVgIxE0GskyA2EgpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [cat = _t, #"01/04/2022" = _t, #"01/05/2022" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"01/04/2022", Int64.Type}, {"01/05/2022", Int64.Type}})
in
    #"Changed Type"

And appending as follows:

let
    Source = Table.Combine({aTable, bTable, cTable})
in
    Source

I get the following output, which looks like what you want, right?

Pete