[Solved] Excel-AddIn 'Power Query' in OpenOffice Calc
[Solved] Excel-AddIn 'Power Query' in OpenOffice Calc
Hello dear Openoffice-Community,
when I work with Excel I use a special Add-In called 'Power Query' for de-pivotation of cross tables.
If you click on the link you can see step-by-step what I do in Excel with 'Power Query'.
http://imgur.com/a/lMFbR
The tables is depivoted with one click and the attributes and their values are line by line mapped.
With the normal pivot-function in OpenOffice / Excel is this not possible.
Do you know a extension for LibreOffice that allows me the same depivotation like Power Query in Excel.
Kind regards
OtenMoten
when I work with Excel I use a special Add-In called 'Power Query' for de-pivotation of cross tables.
If you click on the link you can see step-by-step what I do in Excel with 'Power Query'.
http://imgur.com/a/lMFbR
The tables is depivoted with one click and the attributes and their values are line by line mapped.
With the normal pivot-function in OpenOffice / Excel is this not possible.
Do you know a extension for LibreOffice that allows me the same depivotation like Power Query in Excel.
Kind regards
OtenMoten
Last edited by Hagar Delest on Mon Sep 04, 2017 10:46 pm, edited 1 time in total.
Reason: tagged [Solved].
Reason: tagged [Solved].
OpenOffice 4.1.3 on Windows 10
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Excel-AddIn 'Power Query' in OpenOffice Calc
Welcome to the forum!
As far as I can remember, something similar with not very complicated formulas Andreas Säger did ten years ago. Villeroy gave the link to the file in this message
As far as I can remember, something similar with not very complicated formulas Andreas Säger did ten years ago. Villeroy gave the link to the file in this message
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Re: Excel-AddIn 'Power Query' in OpenOffice Calc
Thanks for the hint!JohnSUN-Pensioner wrote:Welcome to the forum!
As far as I can remember, something similar with not very complicated formulas Andreas Säger did ten years ago. Villeroy gave the link to the file in this message
It does not solved my problem but after spend some more time on Google I found a solution -> next post.
OpenOffice 4.1.3 on Windows 10
Re: Excel-AddIn 'Power Query' in OpenOffice Calc
Hello dear Community,
I found an Extension that do exact the same as Power Query.
It is called 'Decroise' - 1-klick Unpivot
https://extensions.openoffice.org/en/project/decroise
Enjoy!
I found an Extension that do exact the same as Power Query.
It is called 'Decroise' - 1-klick Unpivot
https://extensions.openoffice.org/en/project/decroise
Enjoy!
OpenOffice 4.1.3 on Windows 10
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Excel-AddIn 'Power Query' in OpenOffice Calc
Yes, you are right - this is a good decision. It has some disadvantages - UI language, a limit of 65535 lines, ignoring cells with textual formulas - but it works great.
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Re: Excel-AddIn 'Power Query' in OpenOffice Calc
It is also available in English!JohnSUN-Pensioner wrote:Yes, you are right - this is a good decision. It has some disadvantages - UI language, a limit of 65535 lines, ignoring cells with textual formulas - but it works great.
Open the extension files with 7zip, alter the name of the .DEFAULT language file to en_US.DEFAULT
For the other disadvantages I have no solution but when it is not exceeded then everything is fine^^
OpenOffice 4.1.3 on Windows 10
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Excel-AddIn 'Power Query' in OpenOffice Calc
Yes, I saw English, I meant the rest of the languages.
Other shortcomings are also fixed quite simply. For example, the restriction of 65535 lines can be changed if we replace this constant (line 439 and other) with something similar to oCalcDoc.Sheets.getByIndex(0).getRows().getCount()
For textual formulas we need to slightly complicate the long If-not-and-not-and... in lines 267-269 (and in another place a little later)
Other shortcomings are also fixed quite simply. For example, the restriction of 65535 lines can be changed if we replace this constant (line 439 and other) with something similar to oCalcDoc.Sheets.getByIndex(0).getRows().getCount()
For textual formulas we need to slightly complicate the long If-not-and-not-and... in lines 267-269 (and in another place a little later)
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
- MrProgrammer
- Moderator
- Posts: 4908
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Excel-AddIn 'Power Query' in OpenOffice Calc
Hi, and welcome to the forum.
If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Read about converting a contingency table to a simple table in [Tutorial] Rearrange rectangular data values. This would work in OpenOffice or any related derivatives including LibreOffice.OtenMoten wrote:Do you know a extension for LibreOffice that allows me the same depivotation like Power Query in Excel.
If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: Excel-AddIn 'Power Query' in OpenOffice Calc
hi, could you step by step say how to change the language according to your description?OtenMoten wrote:It is also available in English!JohnSUN-Pensioner wrote:Yes, you are right - this is a good decision. It has some disadvantages - UI language, a limit of 65535 lines, ignoring cells with textual formulas - but it works great.
Open the extension files with 7zip, alter the name of the .DEFAULT language file to en_US.DEFAULT
For the other disadvantages I have no solution but when it is not exceeded then everything is fine^^
OpenOffice 3.1 on Windows Vista
Re: [Solved] Excel-AddIn 'Power Query' in OpenOffice Calc
The .oxt extensions are .zip archives really, containing editable XML files and other files. You can edit them; before you install the extension by usage of the extension manager.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: [Solved] Excel-AddIn 'Power Query' in OpenOffice Calc
in the extension manager I do not have the "options" button, I only have "disable" or "remove". Unless I have to do it by opening the winrar otx file, but what and where do I have to change then? I'm sorry but I'm just learning
OpenOffice 3.1 on Windows Vista
Re: [Solved] Excel-AddIn 'Power Query' in OpenOffice Calc
If you are just learning, you should definitively follow the advice given by MrProgrammer. His spreadsheet makes use of some very smart spreadsheet formulas. Even if you don't understand the formulas, it does the job very well.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Excel-AddIn 'Power Query' in OpenOffice Calc
That is ".oxt" (Open eXTension ...or something similar thing...)opening the winrar otx file,
https://wiki.openoffice.org/wiki/Docume ... ile_Format
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.