Power Query Importing Data from CSV file

[Trickyaz1991]

Hi all,
first time here,
nice to meet you all.

I am currently getting my skills up in excel and ripping my hair out doing it.

so the question is I am currently creating a power query for my worksheet when I import data from file(csv file) which I want it to do the following things;
- delete rows with blank and data I don't want ------ I have done this.
- reorganize columns ------- I have done this
- deleted unwanted columns ---- I have done this

the last thing I am trying to do is there is one column on the csv which is column 6 but on the worksheet, I want it be in column 8 so i want some type of column space which wont effect those cells i have formulas for
on column 6 and 7 in worksheet.

hope this makes sense and someone can help me because scratching my hair out doing it.

regards,

Aaron.

 

[sandy666]

re-read your post and imagine you don't have csv, you don't know structure and even you don't know what you want to achieve

 

[Trickyaz1991]

sorry still doesn't help,
can you give me any pointers?

 

[sandy666]

sure
post a links to the shared csv and shared excel file with your query, and expected result via googledrive, onedrive or any similar
detailed describe what you want to achieve
or
use XL2BB to post part of csv data (all columns and ca. 10 rows)

sorry still doesn't help,

How can we help if we don't know what I said in post # 2?

 

[Trickyaz1991]

see if i can explain it abit better.

i am currently using power query on csv file to get rid of all the stuff i dont want.

this is my layout of worksheet;

Customer, Customer Name, Description, order date, quote date, po date, status

Now here is the part that tricks me, the only columns that get imported from my csv is the following:

Customer, Customer Name, Description, order date, status

Now i could of course add blank columns on query editor but my problem is that if i do the columns on my worksheet will lose the formula i have set for it.

let me know what you think.

regards,

Aaron.

 

[sandy666]

Now i could of course add blank columns on query editor but my problem is that if i do the columns on my worksheet will lose the formula i have set for it.

1. you can calculate with these two new columns in PQ (using M-code to achieve what you want) .... or
2. you can add two blank columns, load to the sheet, then right click on this table and select Table - Convert to range, so it will be disconnected from PQ table then you can use standard sheet functions/formulas
3. you cannot use sheet functions in Query Table, it doesn't make sense because it will be lost after refresh

 

[Trickyaz1991]

thanks, Sandy figured it out

next question I have is:

is there a way each time I import excel into workbook for that only new cells be added, in other words

if (new cell imported = older cell), keep
if (new cell imported = not found), add to bottom of worksheet

this will be done by part number like:

if (1001 = 1001) keep cell
if (1001 = not found) add cell to bottom of worksheet

let me know if this is possible

regards,

Aaron

 

[sandy666]

in short - possible but with some conditions

 

[Trickyaz1991]

well this be done in the power query or advanced query? if so what should i look up to learn how to do it?

 

[sandy666]

hm, I don't know what is it "advanced query", I think you mean Advanced Editor. Correct me if I'm wrong.

I'd like to know scenario,

• each csv has the same structure and type of data
• all csvs will be in the same folder and each next will be added there ... or
• each new csv will be treated as single file with the same name as previous csv ... or
• something else

Imho, the first & second scenario is the best

more about PowerQuery aka Get&Transform

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference
• PowerQuery add-in for XL 2010/2013, all above has PQ built-in
• General: Excel Help