Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Paulyeo11
Impactful Individual
Impactful Individual

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
Anonymous
Not applicable

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

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 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

Anonymous
Not applicable

@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

Anonymous
Not applicable

@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

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

 

 

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

10 REPLIES 10
Anonymous
Not applicable

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



Paulyeo11
Impactful Individual
Impactful Individual

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

Anonymous
Not applicable

@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
Impactful Individual
Impactful Individual

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

Anonymous
Not applicable

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

Paulyeo11
Impactful Individual
Impactful Individual

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 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


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

 

 

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


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 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.