0

I have a Power Query connection that's pulling data from a CSV file into an Excel table. I have then added columns to that table in Excel and added some data to those columns manually.

The issue I have is that whenever I refresh the Power Query, the 'manual' data rows don't stay lined up with the Power Query rows. Specifically, it seems that the manual data all gets shunted down a row in relation to the Power Query data, although even that isn't completely consistent.

I tried unselecting the 'Preserve column sort/filter layout' option in External Data Properties, but that went horribly wrong, as it just removes all of the manually added data from the table completely.

The Power Query itself is very simple, it just takes the CSV data, promotes the headers, changes a couple of types and removes a few columns - there's not even any data filtering going on.

Any assistance much appreciated!

newuser2967
  • 316
  • 1
  • 4
  • 15
  • Personally I think this setup is unwise, but the best I can suggest would be to make sure that in the external data properties dialog you have selected the option to insert entire rows for new data. – Rory Mar 18 '21 at 12:47
  • I'll try that, thanks. With regards to it being an unwise setup, the purpose of the manual columns is to manually override the data as sometimes the data coming through from the CSV is incorrect - is there a better way to achieve the same functionality? – newuser2967 Mar 18 '21 at 12:57
  • Ideally I think you'd have a separate table for that with a key that relates it back to the specific item in the source data. – Rory Mar 18 '21 at 13:00
  • Google self referencing tables with power query – Ricardo Diaz Mar 18 '21 at 13:02
  • See https://exceleratorbi.com.au/self-referencing-tables-power-query/ but I think this is a bad move – horseyride Mar 18 '21 at 13:10
  • @horseyride any suggestions on a better way to achieve the same/similar functionality? Is just having a separate table with IDs and manual information, and pulling that data into the Power Query table via VLookup a better approach to take? Assume that's more or less what Rory is suggesting? – newuser2967 Mar 18 '21 at 13:20
  • 1
    A separate table is my preferred way. Not sure where the vlookup would come into it. I'd assume you just read the manual table in, then use a left outer merge on the matching key fields to merge it with the changing CSV data. If there are multiple row in manual source table for the key fields, maybe combine those first see https://stackoverflow.com/questions/53278667/merge-rows-with-group-by-power-query – horseyride Mar 18 '21 at 14:40

0 Answers0