Get & Transform Data (Power Query)

%3CLINGO-SUB%20id%3D%22lingo-sub-284143%22%20slang%3D%22en-US%22%3EGet%20%26amp%3B%20Transform%20Data%20(Power%20Query)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-284143%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3EI%20have%203%20tables%20on%203%20different%20sheets(tabs)%20of%20the%20same%20excel%20file%20and%20I%20and%20I%20have%20them%20appended%20into%20one%20table%20on%20another%20sheet(tab)%20of%20the%20same%20file.%3C%2FP%3E%3CP%3EIssue....%3C%2FP%3E%3CP%3EThe%20columns%20of%20the%20appended%20table%20are%20rounding%20some%20currency%20columns%20to%20the%20nearest%20.00%2C%20which%20is%20not%20what%20I%20want.%20If%20a%20total%20is%20supposed%20to%20be%20%2425.29%2C%20I%20want%20to%20see%20%2425.29.%20But%20instead%20I%20see%20%2425.00.%3C%2FP%3E%3CP%3EI%20have%20tried%20to%20click%20on%20%22Launch%20Power%20Query%20Editor%22%20so%20I%20could%20potentially%20change%20the%20formatting%20of%20the%20columns%2C%20but%20nothing%20happens.%20I%20can't%20seem%20to%20get%20the%20editor%20to%20open%20as%20I%20was%20when%20I%20first%20created%20the%20table.%20How%20do%20I%20get%20the%20Editor%20to%20open%3F%20I%20have%20tried%20clicking%20on%20the%20Query%20in%20the%20list%20of%20Queries%20and%20then%20click%20Edit%2C%20this%20doesn't%20work%20either.%20I%20was%20reading%20some%20other%20posts%20online%20and%20it%20seems%20there%20might%20be%20a%20bug%20with%20Excel%3F%20I%20just%20need%20to%20get%20the%20editor%20to%20open%20so%20I%20can%20edit%20the%20appended%20table%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EInformation......%3C%2FP%3E%3CP%3EThe%203%20original%20tables%20have%20the%20columns%20formatted%20and%20calculating%20correctly.%3C%2FP%3E%3CP%3EThe%20columns%20on%20the%20appended%20table%20are%20formatted%20correctly%2C%20but%20data%20does%20not%20match%20the%20data%20from%20original%20table%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-284143%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EGet%20%26amp%3B%20Transform%20Data%20(Power%20Query)%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-284253%22%20slang%3D%22en-US%22%3ERe%3A%20Get%20%26amp%3B%20Transform%20Data%20(Power%20Query)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-284253%22%20slang%3D%22en-US%22%3E%3CP%3Eyes.%20that%20is%20what%20I%20tried.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-284252%22%20slang%3D%22en-US%22%3ERe%3A%20Get%20%26amp%3B%20Transform%20Data%20(Power%20Query)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-284252%22%20slang%3D%22en-US%22%3EIt%20doesn't%20work.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-284240%22%20slang%3D%22en-US%22%3ERe%3A%20Get%20%26amp%3B%20Transform%20Data%20(Power%20Query)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-284240%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Shawn%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOn%20the%20right%20pane%20with%20list%20of%20queries%20double%20click%20on%20query%20or%20right%20click-%26gt%3Bedit%20to%20open%20the%20query%20editor.%20Is%20that%20what%20you%20tried%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1967296%22%20slang%3D%22en-US%22%3ERe%3A%20Get%20%26amp%3B%20Transform%20Data%20(Power%20Query)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1967296%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239968%22%20target%3D%22_blank%22%3E%40Shawn%20Lorusso%3C%2FA%3E%26nbsp%3BWell%2C%20I%20see%20there%20was%20no%20answer%20to%20this.%20I%20have%20essentially%20the%20same%20question%20in%20December%202020.%20My%20installation%20of%20Excel%20is%20from%20my%20Office%20365%20Family%20version.%20Everything%20is%20up%20to%20date.%20Everything%20else%20works%20fine.%20I%20had%20not%20used%20getting%20data%20for%20quite%20some%20time%20but%20I%20wanted%20to%20import%20data%20from%20a%20text%20file%20and%20perhaps%20see%20the%20old%20time%20legacy%20wizard%20that%20worked%20just%20fine%20for%20the%20years%20I%20had%20used%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWindows%2010%20Pro%2C%2064%20bit%20installation%2C%20as%20I%20said%20everything%20is%20up%20to%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20never%20get%20to%20the%20text%20file.%20I%20open%20an%20empty%20new%20workbook%20and%20go%20to%20Data%2C%20Get%20Data%3B%20select%20from%20file%20and%20then%20the%20csv%2Ftxt%20option.%20What%20I%20get%20is%20a%20dialog%20message%20telling%20me%20Excel%20is%20not%20properly%20installed%20and%20to%20please%20repair%20or%20reinstall%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20may%20add%20that%20I%20tried%20both%20with%20the%20online%20version%20and%20my%20local%20installation%2C%20getting%20the%20same%20message%20either%20way.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20I%20went%20to%20the%20Control%20Panel%20and%20found%20the%20Office%20installation%2C%20selected%20change.%20I%20first%20tried%20the%20quick%20repair.%20No%20change.%20Then%20I%20tried%20the%20online%20repair.%20Same%20result.%20I%20did%20a%20full%20reinstallation%20from%20my%20Account.%20Same%20result%20every%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20contacted%20support%20chat%20and%20after%20a%20half%20hour%20of%20doing%20exactly%20what%20I%20had%20already%20done%2C%20the%20support%20person%20'escalated'%20the%20problem%20and%20put%20me%20in%20touch%20with%20someone%20who%20could%20provide%20technical%20assistance.%20What%20was%20he%20providing%20for%20the%20first%20half%20hour%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20the%20new%20technical%20support%20guy%20wanted%20to%20take%20remote%20control%20for%20troubleshooting!%20I%20declined%2C%20telling%20him%20I%20was%20not%20a%20newbie%20and%20anything%20he%20could%20express%20I%20could%20do%20more%20quickly%20than%20he%20could.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHe%20proceeded%20to%20make%20suggestions%20and%20I%20proved%20to%20him%20I%20could%20conduct%20them%20all%20faster%20than%20even%20he%20could.%20He%20went%20through%20the%20same%20stuff%20the%20previous%20non-tech%20guy%20did%2C%20then%20sent%20me%20links%20recommending%20I%20test%20the%20installations%20by%20creating%20a%20new%20Administrative%20user.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20did%20that%20and%20the%20tests%20still%20came%20up%20with%20the%20same%20result.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAt%20that%20point%20he%20recommended%20a%20complete%20un-installation%20and%20new%20clean%20installation%20of%20Office%20365.%20He%20sent%20me%20a%20link%20to%20an%20article%20that%20basically%20recited%20the%20Microsoft%20policy%20of%20not%20supporting%20any%20registry%20edits%20and%20their%20disclaimer%20of%20any%20responsibility%20if%20following%20their%20instructions%20broke%20my%20Windows%20installation!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat's%20a%20weasel%20cop%20out.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%2C%20I%20can%20do%20that%2C%20but%20I%20guarantee%20you%20that%20I%20didn't%20do%20anything%20to%20change%20the%20registry%20before%20this%20happened.%20What%20happened%20to%20the%20OS%20doing%20its%20checks%20and%20repairs%20of%20corrupt%20system%20or%20Microsoft's%20own%20software%20installation%20files%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20drives%20are%20relatively%20new.%20They%20all%20have%20SMART.%20I%20ran%20a%20Performance%20Monitor%20System%20Diagnostic%20and%20everything%20Passed%20without%20any%20warnings%20or%20errors.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESeriously%2C%20WTF%3F%20Does%20anybody%20have%20a%20real%20idea%20of%20what%20is%20going%20on%20here%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20saved%20the%20chat%20session%20and%20am%20planning%20to%20send%20it%20to%20Microsoft%20Support%20as%20a%20Feedback.%20I%20can't%20believe%20they%20have%20no%20fix%20for%20this!%20They%20don't%20seem%20to%20have%20a%20clue%20themselves!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1967468%22%20slang%3D%22en-US%22%3ERe%3A%20Get%20%26amp%3B%20Transform%20Data%20(Power%20Query)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1967468%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F48116%22%20target%3D%22_blank%22%3E%40Rafael%20Silva%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20mean%20this%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Funinstall-office-from-a-pc-9dd49b83-264a-477a-8fcc-2fdf5dbf61d8%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EUninstall%20Office%20from%20a%20PC%20-%20Office%20Support%20(microsoft.com)%3C%2FA%3E%26nbsp%3Barticle%20with%20Option%202%20for%20complete%20uninstall%20-%20that's%20quite%20standard%20tool%20if%20online%20repair%20doesn't%20help%20and%20I%20didn't%20here%20there%20are%20any%20issues%20with%20it.%3C%2FP%3E%0A%3CP%3ENew%20installation%20which%20you%20did%20on%20the%20top%20of%20previous%20one%20and%20online%20repair%20do%20the%20same%2C%20they%20re-install%20Office%20on%20keeping%20your%20profile%20data%20from%20previous%20installation%2C%20thus%20not%20issues%20could%20be%20fixed%2C%20complete%20uninstall%20is%20required.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20more%20comfortable%20with%20legacy%20wizard%20you%20may%20activate%20it%20in%20File-%26gt%3BOptions%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20637px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F238677i72FC108D104AB090%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%20use%20it%20instead%20of%20Power%20Query%20from%20here%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20479px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F238679iA55BEDD841DB4753%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EJust%20Power%20Query%20is%20much%20more%20powerful.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello

I have 3 tables on 3 different sheets(tabs) of the same excel file and I and I have them appended into one table on another sheet(tab) of the same file.

Issue....

The columns of the appended table are rounding some currency columns to the nearest .00, which is not what I want. If a total is supposed to be $25.29, I want to see $25.29. But instead I see $25.00.

I have tried to click on "Launch Power Query Editor" so I could potentially change the formatting of the columns, but nothing happens. I can't seem to get the editor to open as I was when I first created the table. How do I get the Editor to open? I have tried clicking on the Query in the list of Queries and then click Edit, this doesn't work either. I was reading some other posts online and it seems there might be a bug with Excel? I just need to get the editor to open so I can edit the appended table

 

Information......

The 3 original tables have the columns formatted and calculating correctly.

The columns on the appended table are formatted correctly, but data does not match the data from original table

5 Replies

Hi Shawn,

 

On the right pane with list of queries double click on query or right click->edit to open the query editor. Is that what you tried?

It doesn't work.

yes. that is what I tried.

@Shawn Lorusso Well, I see there was no answer to this. I have essentially the same question in December 2020. My installation of Excel is from my Office 365 Family version. Everything is up to date. Everything else works fine. I had not used getting data for quite some time but I wanted to import data from a text file and perhaps see the old time legacy wizard that worked just fine for the years I had used it.

 

Windows 10 Pro, 64 bit installation, as I said everything is up to date.

 

I never get to the text file. I open an empty new workbook and go to Data, Get Data; select from file and then the csv/txt option. What I get is a dialog message telling me Excel is not properly installed and to please repair or reinstall it.

 

I may add that I tried both with the online version and my local installation, getting the same message either way.

 

So, I went to the Control Panel and found the Office installation, selected change. I first tried the quick repair. No change. Then I tried the online repair. Same result. I did a full reinstallation from my Account. Same result every time.

 

I contacted support chat and after a half hour of doing exactly what I had already done, the support person 'escalated' the problem and put me in touch with someone who could provide technical assistance. What was he providing for the first half hour?

 

Then the new technical support guy wanted to take remote control for troubleshooting! I declined, telling him I was not a newbie and anything he could express I could do more quickly than he could. 

 

He proceeded to make suggestions and I proved to him I could conduct them all faster than even he could. He went through the same stuff the previous non-tech guy did, then sent me links recommending I test the installations by creating a new Administrative user.

 

I did that and the tests still came up with the same result.

 

At that point he recommended a complete un-installation and new clean installation of Office 365. He sent me a link to an article that basically recited the Microsoft policy of not supporting any registry edits and their disclaimer of any responsibility if following their instructions broke my Windows installation!

 

That's a weasel cop out.

 

Yes, I can do that, but I guarantee you that I didn't do anything to change the registry before this happened. What happened to the OS doing its checks and repairs of corrupt system or Microsoft's own software installation files?

 

My drives are relatively new. They all have SMART. I ran a Performance Monitor System Diagnostic and everything Passed without any warnings or errors.

 

Seriously, WTF? Does anybody have a real idea of what is going on here?

 

I saved the chat session and am planning to send it to Microsoft Support as a Feedback. I can't believe they have no fix for this! They don't seem to have a clue themselves!

@Rafael Silva 

If you mean this Uninstall Office from a PC - Office Support (microsoft.com) article with Option 2 for complete uninstall - that's quite standard tool if online repair doesn't help and I didn't here there are any issues with it.

New installation which you did on the top of previous one and online repair do the same, they re-install Office on keeping your profile data from previous installation, thus not issues could be fixed, complete uninstall is required.

 

If you more comfortable with legacy wizard you may activate it in File->Options 

image.png

and use it instead of Power Query from here

image.png

Just Power Query is much more powerful.