cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Prodigy
Post Prodigy

Best approach in combine 2 raw data into 1 ?

Hi All

What is the best approach to combine both 2 CSV file into 1 ?

Paulyeo11_0-1603867102254.png

 

5 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Memorable Member
Memorable Member

Hi @Paulyeo11 

You can make use of Append  since both the tables follow same schema

Table1
NameSales

A10
B20
C30
D40


Table2
NameSales

A30
B50
H60

 

Append option in Power query

append12.png

After append
afterappend.png

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!!👍



View solution in original post

Highlighted

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 

View solution in original post

Highlighted

@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.

View solution in original post

Highlighted

@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:-)

View solution in original post

Highlighted

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

 

 

 

View solution in original post

10 REPLIES 10
Highlighted
Memorable Member
Memorable Member

Hi @Paulyeo11 

You can make use of Append  since both the tables follow same schema

Table1
NameSales

A10
B20
C30
D40


Table2
NameSales

A30
B50
H60

 

Append option in Power query

append12.png

After append
afterappend.png

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!!👍



View solution in original post

Highlighted

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 

View solution in original post

Highlighted

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

Highlighted

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

Highlighted

@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.

View solution in original post

Highlighted

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

Highlighted

@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:-)

View solution in original post

Highlighted

Hi Hus

I also owe you one brink , thank you very much.

Paul

Highlighted

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

 

 

 

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors