Power Query When Tables Don't Match

[PaulFogel123]

Hello,

I have a database (a small sample is attached. The actual database runs hundreds of thousands of rows). In that database are calculated fields (such as the day of the week), and lookup formulas that reference tables in other parts of the file. My Excel model uses these calculated an reference fields in many places. What I want to do eventually is keep the raw data (without calculated fields or lookup formulas) in the cloud and have Power Query import it into Excel. To test it, I created an Excel database of the raw data and then tried importing (just like in the file attached). But I ran into trouble. If the source table and the destination table have different fields, as they do in this case, it doesn't work. I could import successfully if the tables had the same headers.

Does anyone have a possible solution to this problem?

 

[Chihiro]

So, what's the expected result? Where are the lookup table/files?

PQ is not a database tool, but should be used as transformation tool. It isn't clear to me what you are trying to achieve here.

 

[PaulFogel123]

I want to import or append the data in the Import DB tab to the table on the Sample DB tab.

 

[AlanSidman]

Why not a simple copy and paste and append the Import sheet to the Sample

 

[PaulFogel123]

I will have several Excel models to maintain, each of whose data will be in separate, secure SQL databases. The Excel models will reside within Azure on a virtual machine. I might be able to do copy and paste, but at hundreds of thousands of rows to copy for each Excel model, this would be cumbersome.

 

[AlanSidman]

In PQ, I brought each table into PQ and then appended one to the other. Seems to have worked. Here is the code

let
    Source = Excel.CurrentWorkbook(){[Name="DestinationDB"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Financial Class", type text}, {"Maps To", type text}, {"DRG", Int64.Type}, {"DRG Wt", type number}, {"MDC", Int64.Type}, {"Type", type text}, {"Admission Date", type datetime}, {"Discharge Date", type datetime}, {"Cases", Int64.Type}, {"DischYear", Int64.Type}, {"DischMonthYear", Int64.Type}, {"Admission Day", type text}, {"Discharge Day", type text}, {"LOS", Int64.Type}, {"CMS LOS", type number}}),
    #"Appended Query" = Table.Combine({#"Changed Type", ImportDB})
in
    #"Appended Query"

 

[PaulFogel123]

Thanks, Alan. I’m not at my computer till tomorrow and I will follow up then.

 

[PaulFogel123]

I must've done it incorrectly last time. I imported the data from the two tables and then appended one to the other, using the commands from the ribbon. The ImportDB table data was appended to the correct headings in the DestinationDB table. Excel named the new appended table Append1. In my workbook, formulas search for data using the original table name--DestinationDB. Can this name change be prevented? Or I might need to rename the table manually.

I didn't run the code you produced. What does it do? If it carries out the same operations, I could use it to automate the procedure.

I do have a question: In the Sample DB tab, I changed the DischYear column to a formula (=YEAR([@[Discharge Date]])). I wanted to see how PQ would handle it, and it converted the formula to a number. What I'd like is to have the formula preserved and copied all the way down to the last row of appended data. Is that something PQ can do or does that require code?

Thanks,Paul

 

[Chihiro]

I'd not recommend using formula in conjunction with PowerQuery generated table. Unless columns containing formulas are all set to right of PowerQuery generated columns.

Instead, use custom columns and join operations within PowerQuery editor to return desired result.

But, without sample lookup tables and calculation required, can't really help you on exact steps.

 

[PaulFogel123]

I modified the Excel file to include the tables, one to a tab, where the green columns are referencing. Below the DestinationDB table is a key where they can be found. I left some formulas intact so you will get external links warnings.
Thanks

 

[GraH - Guido]

Hi Paul, you say you were using "append" but in your last file the green columns are "lookups". Try to use "merge" instead. Same place on the the ribbon.
Just saw Chihiro's comment to use join, it is the same deal.