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

[Hagar Delest]

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

[JohnSUN-Pensioner]

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

[OtenMoten]

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.

[OtenMoten]

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!

[JohnSUN-Pensioner]

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.

[OtenMoten]

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.

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

[JohnSUN-Pensioner]

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)

[MrProgrammer]

Hi, and welcome to the forum.

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.

[jdsz]

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.

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?

[Zizi64]

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.

[jdsz]

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

[Villeroy]

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.

[Zizi64]

opening the winrar otx file,

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

https://wiki.openoffice.org/wiki/Docume ... ile_Format