Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi @Paulyeo11
You can make use of Append since both the tables follow same schema
Table1
NameSales
A | 10 |
B | 20 |
C | 30 |
D | 40 |
Table2
NameSales
A | 30 |
B | 50 |
H | 60 |
Append option in Power query
After append
Hope it resolves your issue? Please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos, Press the thumbs up button!!👍
Hi @Paulyeo11
If both files have the same columns, place them both in a folder then import and combine them
let
Source = Folder.Files("D:\temp\paulyeo11"),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}),
#"Filtered Hidden Files1" = Table.SelectRows(#"Removed Other Columns", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from paulyeo11", each #"Transform File from paulyeo11"([Content])),
#"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File from paulyeo11"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from paulyeo11", Table.ColumnNames(#"Transform File from paulyeo11"(#"Sample File")))
in
#"Expanded Table Column1"
Phil
Proud to be a Super User!
@Paulyeo11 You can rename the column names in power query...No you dont have to consider the script in the screenshot ...To give you an example of append i had to delete my previous data you can ignore the delete script.
@Paulyeo11 Yes both the table column names must be same.
If you click on append it will try to add new column in the exisiting table in my example table 1
If you click on append as new query it will create a fresh table and append both the tables into a new one choice is yours:-)
Hi @Paulyeo11
It's a bit of both. To start with you click Get Data ->More -> Folder.
Browse to the folder. You get a list of the files in that folder.
I then clicked Edit.
Right click on the header of the first column named Content and then Remove other columns
Click the double headed down arrow on the Content column header
Enter the correct numbers for the columns in the fixed width import window {0, 9, 68, 72, 99, 119, 126, 161}
Click OK and it combines the files.
All of those steps generate M code which is the query you see in the Advanced Editor. PBI creates this M code from the pointing and clicking that you do.
Phil
Proud to be a Super User!
Hi @Paulyeo11
You can make use of Append since both the tables follow same schema
Table1
NameSales
A | 10 |
B | 20 |
C | 30 |
D | 40 |
Table2
NameSales
A | 30 |
B | 50 |
H | 60 |
Append option in Power query
After append
Hope it resolves your issue? Please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos, Press the thumbs up button!!👍
Hi Hus
In your example , the field name are similar for both Table. But mine the field name are different. Secondly i notice that after append the 2 table into 1 , in your second screen shot , you need to write script to delete the extra column right ?
Paul
@Paulyeo11 You can rename the column names in power query...No you dont have to consider the script in the screenshot ...To give you an example of append i had to delete my previous data you can ignore the delete script.
Hi Hus
So in order to append the 2 table .
Step 1 Rename the both table field to similar , like Company_name and Company , bith must be Company
Step 2 Append the 2 Table as per your screen shot
it is correct approach ?
Paul
@Paulyeo11 Yes both the table column names must be same.
If you click on append it will try to add new column in the exisiting table in my example table 1
If you click on append as new query it will create a fresh table and append both the tables into a new one choice is yours:-)
Hi Hus
I also owe you one brink , thank you very much.
Paul
Hi @Paulyeo11
If both files have the same columns, place them both in a folder then import and combine them
let
Source = Folder.Files("D:\temp\paulyeo11"),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}),
#"Filtered Hidden Files1" = Table.SelectRows(#"Removed Other Columns", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from paulyeo11", each #"Transform File from paulyeo11"([Content])),
#"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File from paulyeo11"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from paulyeo11", Table.ColumnNames(#"Transform File from paulyeo11"(#"Sample File")))
in
#"Expanded Table Column1"
Phil
Proud to be a Super User!
Hi Philip
Thank you very much for sharing those script , which are very long. May i know Are those script auto generated or manually created ?
Paul
Hi @Paulyeo11
It's a bit of both. To start with you click Get Data ->More -> Folder.
Browse to the folder. You get a list of the files in that folder.
I then clicked Edit.
Right click on the header of the first column named Content and then Remove other columns
Click the double headed down arrow on the Content column header
Enter the correct numbers for the columns in the fixed width import window {0, 9, 68, 72, 99, 119, 126, 161}
Click OK and it combines the files.
All of those steps generate M code which is the query you see in the Advanced Editor. PBI creates this M code from the pointing and clicking that you do.
Phil
Proud to be a Super User!
Hi Philip
Wow these step are very important to follow, in order to save me a lot of time in the future. I think load folder is the must use.
Thank you very much.
Paul
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |