Any general way to map a field in query editor?

[Anonymous]

Hi everyone,

As a follow up to this post ( https://community.powerbi.com/t5/Desktop/Extracting-data-from-same-Excel-template-files/m-p/667512#M... ), I managed to have pretty much every file working. However, I need to move the dates from Column 8, to Column 2. How can I do that? If I go with replace value, then this rule stays and query editor follows the steps of the first imported excel file and applies this formula to all others from the folder that I additionally import.

In excel this would be very straight-forward, Column G, Row 6 -> Column B, Row 6. Anybody had similar issue?

Many thanks,

Filip

[MFelix]

Hi @Anonymous,

Don't know if I understood your question correctly but you just want to change the positions of the columns?

For that you just need to drag and drop the columns on the query editor this will create an additional step on the query with reordering.

Or do you want to replace the date column in a single file only and the rest will kepp as is?

Regards, Mfelix

[Anonymous]

Hi @MFelix ,

What I want to do is simply make a step where I say:

Replace values from Column 8, Row 6 to Column 2, Row 6.

If I do replace value function, it will always replace with the value I initially put in. I would like to replace a value that is different date in each table.

In case it is not clear, I would try to upload something here to show you exactly where the problem is.

Thank you for your time.

Filip

[MFelix]

Hi @Anonymous ,

If you can share an example of data and expected result would be grreat.

Regards,

MFelix

[Anonymous]

Hi @MFelix ,

Here is the screenshot from Query Editor. This is my original file from which I started and after, applied all the steps here for the whole folder by using functions/parameters ( https://www.youtube.com/watch?v=GgwXt4LVmsU )

1st step is that I go to replace text and this is easy, because in all excel files (cca. 200) they have the same name for Rows in Column1. 2nd step is that I do the same thing for Row 6 and this holds very well. However, the problem is how to put a generic command to replace data (date format) from Column 8, Row 5 to Column 2, Row 5 and respectively, Column 8, Row 6 to Column 2, Row 6.

At the moment, I use the same function as with replace value (text, formula in the tab) and then I get the same dates in all of remaining 200 files. Obviously way of doing applied here is wrong and obvious that it is what I get but I cannot figure out the way around it.

I hope this clarifies the question a bit. Many thanks for your time.

Best,
Filip

[MFelix]

Hi @Anonymous ,

Excuse my questions but just to understand the issue:

You want to replace the 3 (column 2 value) by 08.03.2019 (column 8 value)? Is this correct?

Regards,

MFelix

[Anonymous]

Hi @MFelix 

Yes, exactly. And the same thing to the row below.

[MFelix]

Hi,

Sorry for the additional questions do you only want to have this change for those specific 2 lines or for the full columns?
You want to keep two columns with the same values?

One option is to create a duplicate of column 8 delete column 2 and rename the duplicate of column 8 to column 2. But this will overwrite all rows not only 6 and 5

Regards,
MFelix