Excel Get and Transform Refresh does not Refresh

%3CLINGO-SUB%20id%3D%22lingo-sub-1521100%22%20slang%3D%22en-US%22%3EExcel%20Get%20and%20Transform%20Refresh%20does%20not%20Refresh%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1521100%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20Morning%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20an%20Excel%20workbook.%26nbsp%3B%20It%20has%203%20tables.%3C%2FP%3E%3CP%3ETable%201%20I%20populate%20by%20cutting%20and%20pasting%20data%20from%201%20data%20source%3C%2FP%3E%3CP%3ETable%202%20I%20populate%20by%20cutting%20and%20pasting%20data%20from%20a%20different%20data%20source%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20then%20use%20Get%20and%20Transform%20to%20take%20Tables%201%20and%202%20and%20append%20them%20into%20Table%203.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20built%20it%20worked%20wonderfully.%26nbsp%3B%20Now%20I%20am%20updating%20the%20data%20in%20Tables%201%20and%202%20and%20click%20Refresh%20All%20but%20it%20won't%20update%20Table%203.%26nbsp%3B%20I%20have%20tried%20saving%20the%20workbook%20and%20then%20Refreshing.%26nbsp%3B%20I%20tried%20Saving%2C%20closing%2C%20and%20then%20re-opening%20and%20refreshing.%20Nothing%20I%20do%20will%20get%20Table%203%20to%20update%20even%20though%20Excel%20keeps%20saying%20%22Running%20Background%20Query%20.%20.%20.%20%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20it%20helps%20this%20is%20using%20Excel%20for%20Office%20365%2064%20Bit%3C%2FP%3E%3CP%3ETransform%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1521100%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1521303%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Get%20and%20Transform%20Refresh%20does%20not%20Refresh%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1521303%22%20slang%3D%22en-US%22%3ECheck%20the%20definition%20of%20the%20append%20is%20correctly%20pointing%20towards%20the%20pasted%20data.%20Are%20you%20sure%20that%20you%20are%20pasting%20into%20the%20Tables%20themselves%2C%20and%20not%20pasting%20over%20them%3F%20If%20you%20pasted%20over%20them%2C%20the%20Append%20query%20would%20not%20work.%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20you're%20able%20to%20attach%20your%20workbook%20to%20your%20post%2C%20I'll%20take%20a%20look%20at%20it.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1533458%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Get%20and%20Transform%20Refresh%20does%20not%20Refresh%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1533458%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20Morning%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F725726%22%20target%3D%22_blank%22%3E%40OwenPrice%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20offer%20to%20review%20it.%26nbsp%3B%20I%20kept%20at%20it%20and%20was%20able%20to%20get%20it%20to%20work.%3C%2FP%3E%3CP%3EIn%20case%20anyone%20needs%20a%20simple%20way%20to%20append%202%20tables%20here%20is%20the%20Let%20statement.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Elet%3CBR%20%2F%3ESource%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Table1%22%5D%7D%5BContent%5D%2C%3CBR%20%2F%3E%23%22Appended%20Query%22%20%3D%20Table.Combine(%7BSource%2C%20Excel.CurrentWorkbook()%7B%5BName%3D%22Table2%22%5D%7D%5BContent%5D%7D)%2C%3CBR%20%2F%3E%23%22Changed%20Type%22%20%3D%20Table.TransformColumnTypes(%23%22Appended%20Query%22%2C%7B%7B%22Field01%22%2C%20type%20text%7D%2C%20%7B%22Field02%22%2C%20type%20text%7D%2C%20%7B%22Field03%22%2C%20type%20number%7D%2C%20%7B%22Source%22%2C%20type%20text%7D%7D)%3CBR%20%2F%3Ein%3CBR%20%2F%3E%23%22Changed%20Type%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESample%20workbook%20attached.%26nbsp%3B%20It%20takes%20the%20data%20from%20Table1%20and%20Table2%20and%20combines%20them%20into%20Table%203.%26nbsp%3B%20Then%20a%20pivot%20table%20sourced%20from%20Table3%20to%20do%20whatever%20analysis%20you%20need.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3CP%3EBarclay%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Good Morning,

 

I have an Excel workbook.  It has 3 tables.

Table 1 I populate by cutting and pasting data from 1 data source

Table 2 I populate by cutting and pasting data from a different data source

 

I then use Get and Transform to take Tables 1 and 2 and append them into Table 3.

 

When I built it worked wonderfully.  Now I am updating the data in Tables 1 and 2 and click Refresh All but it won't update Table 3.  I have tried saving the workbook and then Refreshing.  I tried Saving, closing, and then re-opening and refreshing. Nothing I do will get Table 3 to update even though Excel keeps saying "Running Background Query . . . "

 

If it helps this is using Excel for Office 365 64 Bit

Transform

Thank you

 

 

2 Replies
Check the definition of the append is correctly pointing towards the pasted data. Are you sure that you are pasting into the Tables themselves, and not pasting over them? If you pasted over them, the Append query would not work.

If you're able to attach your workbook to your post, I'll take a look at it.

Good Morning @OwenPrice,

 

Thank you for the offer to review it.  I kept at it and was able to get it to work.

In case anyone needs a simple way to append 2 tables here is the Let statement.

 

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Appended Query" = Table.Combine({Source, Excel.CurrentWorkbook(){[Name="Table2"]}[Content]}),
#"Changed Type" = Table.TransformColumnTypes(#"Appended Query",{{"Field01", type text}, {"Field02", type text}, {"Field03", type number}, {"Source", type text}})
in
#"Changed Type"

 

Sample workbook attached.  It takes the data from Table1 and Table2 and combines them into Table 3.  Then a pivot table sourced from Table3 to do whatever analysis you need.

 

Thank you.

Barclay