[Solved] Excel-AddIn 'Power Query' in OpenOffice Calc

Discuss the spreadsheet application

[Solved] Excel-AddIn 'Power Query' in OpenOffice Calc

Postby OtenMoten » Tue Aug 22, 2017 9:11 am

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
Last edited by Hagar Delest on Mon Sep 04, 2017 10:46 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 4.1.3 on Windows 10
OtenMoten
 
Posts: 4
Joined: Tue Aug 22, 2017 9:03 am

Re: Excel-AddIn 'Power Query' in OpenOffice Calc

Postby JohnSUN-Pensioner » Tue Aug 22, 2017 9:50 am

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
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.4, LibreOffice 5.4.2.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 761
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Excel-AddIn 'Power Query' in OpenOffice Calc

Postby OtenMoten » Tue Aug 22, 2017 10:56 am

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


Thanks for the hint!
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
OtenMoten
 
Posts: 4
Joined: Tue Aug 22, 2017 9:03 am

Re: Excel-AddIn 'Power Query' in OpenOffice Calc

Postby OtenMoten » Tue Aug 22, 2017 11:00 am

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! :-)
OpenOffice 4.1.3 on Windows 10
OtenMoten
 
Posts: 4
Joined: Tue Aug 22, 2017 9:03 am

Re: Excel-AddIn 'Power Query' in OpenOffice Calc

Postby JohnSUN-Pensioner » Tue Aug 22, 2017 11:32 am

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. :super:
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.4, LibreOffice 5.4.2.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 761
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Excel-AddIn 'Power Query' in OpenOffice Calc

Postby OtenMoten » Tue Aug 22, 2017 11:35 am

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. :super:


It is also available in English!

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
OtenMoten
 
Posts: 4
Joined: Tue Aug 22, 2017 9:03 am

Re: Excel-AddIn 'Power Query' in OpenOffice Calc

Postby JohnSUN-Pensioner » Tue Aug 22, 2017 12:10 pm

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)
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.4, LibreOffice 5.4.2.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 761
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Excel-AddIn 'Power Query' in OpenOffice Calc

Postby MrProgrammer » Wed Aug 23, 2017 5:03 am

Hi, and welcome to the forum.

OtenMoten wrote:Do you know a extension for LibreOffice that allows me the same depivotation like Power Query in Excel.
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.

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 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3896
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Excel-AddIn 'Power Query' in OpenOffice Calc

Postby jdsz » Wed Jul 10, 2019 10:17 am

OtenMoten wrote:
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. :super:


It is also available in English!

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^^


hi, could you step by step say how to change the language according to your description?
OpenOffice 3.1 on Windows Vista
jdsz
 
Posts: 2
Joined: Wed Jul 10, 2019 10:12 am

Re: [Solved] Excel-AddIn 'Power Query' in OpenOffice Calc

Postby Zizi64 » Wed Jul 10, 2019 11:16 am

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; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
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.
User avatar
Zizi64
Volunteer
 
Posts: 8538
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Solved] Excel-AddIn 'Power Query' in OpenOffice Calc

Postby jdsz » Wed Jul 10, 2019 11:39 am

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
jdsz
 
Posts: 2
Joined: Wed Jul 10, 2019 10:12 am

Re: [Solved] Excel-AddIn 'Power Query' in OpenOffice Calc

Postby Villeroy » Wed Jul 10, 2019 11:54 am

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27380
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Excel-AddIn 'Power Query' in OpenOffice Calc

Postby Zizi64 » Wed Jul 10, 2019 12:17 pm

opening the winrar otx file,


That is ".oxt" (Open eXTension ...or something similar thing...)

https://wiki.openoffice.org/wiki/Docume ... ile_Format
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
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.
User avatar
Zizi64
Volunteer
 
Posts: 8538
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary


Return to Calc

Who is online

Users browsing this forum: No registered users and 21 guests