topic Re: Need help to manipulate this table correctly in Power Query

Need help to manipulate this table correctly

Goatsu — Wed, 12 Jun 2024 08:29:55 GMT

Hello dear community,

My database extract looks like this:

This consists of the dimensions:

Vergabe ID

Kurzbezeichnung

Kostenjahraufteilung1,2,3 etc. (could be infinite) (each Kostenjahraufteilung and number is a separate column)
Kostenjahraufteilung = Year

Kostenaufteilung1,2,3, etc. (could be infinite) (each Kostenaufteilung and number is a separate column)
Kostenaufteilung = Cost

And i want it to look like this, so I can pivot the data through excel:

I tried so much already and i just cant seem to find a solution.

Thank you all in advance!

Cheers

Re: Need help to manipulate this table correctly

AlienSx — Wed, 12 Jun 2024 09:08:02 GMT

let Source = your_table, to_list = Table.ToList( Source, (w) => List.TransformMany( {w}, (x) => List.Zip(List.Split(List.Skip(x, 2), (Table.ColumnCount(Source) - 2) / 2)), (x, y) => {x{0}, x{1}} & y ) ), to_table = Table.FromList(List.Combine(to_list), (x) => x, {"Vergabe ID", "Kurzbezeichnung", "Year", "Cost"}) in to_table

Re: Need help to manipulate this table correctly

ManuelBolz — Wed, 12 Jun 2024 09:08:28 GMT

Hello Goatsu,

ich habe dir im folgenden mal eine Lösung erstellt. Wenn du Rückfragen hast, melde dich gerne. Du musst bei meiner Lösung deine Daten im SOURCE Schritt ersetzten.

If my post helped you, please give me a 👍kudos and mark this post with Accept as Solution.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LcnBDcAgDATBXvxOJINtTGpB9N9GHDav0d6tJV273aoul5hmFrU4BAz4vwnPwRRa0UKpg4FDwICE+bH3Cw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Kurzbezeichnung = _t, VergabeID = _t, KOSTENJAHR_AUFTEILUNG1 = _t, KOSTENJAHR_AUFTEILUNG2 = _t, KOSTENJAHR_AUFTEILUNG3 = _t, KOSTENJAHR_AUFTEILUNG4 = _t, KOSTENJAHR_AUFTEILUNG5 = _t, KOSTENJAHR_AUFTEILUNG6 = _t, KOSTENJAHR_AUFTEILUNG7 = _t, KOSTENJAHR_AUFTEILUNG8 = _t, KOSTEN_AUFTEILUNG1 = _t, KOSTEN_AUFTEILUNG2 = _t, KOSTEN_AUFTEILUNG3 = _t, KOSTEN_AUFTEILUNG4 = _t, KOSTEN_AUFTEILUNG5 = _t, KOSTEN_AUFTEILUNG6 = _t, KOSTEN_AUFTEILUNG7 = _t, KOSTEN_AUFTEILUNG8 = _t]), KOSTENJAHRTable = Table.SelectRows(Table.UnpivotOtherColumns(Source, {"Kurzbezeichnung", "VergabeID"}, "Attribute", "Year"), each Text.Contains([Attribute], "KOSTENJAHR_")), KOSTENJAHRReplace = Table.ReplaceValue(KOSTENJAHRTable,"KOSTENJAHR_AUFTEILUNG","",Replacer.ReplaceText,{"Attribute"}), KOSTENJAHRType = Table.TransformColumnTypes(KOSTENJAHRReplace,{{"Attribute", Int64.Type}}), KOSTENTable = Table.SelectRows(Table.UnpivotOtherColumns(Source, {"Kurzbezeichnung", "VergabeID"}, "Attribute", "Cost"), each Text.Contains([Attribute], "KOSTEN_")), KOSTENReplace = Table.ReplaceValue(KOSTENTable,"KOSTEN_AUFTEILUNG","",Replacer.ReplaceText,{"Attribute"}), KOSTENType = Table.TransformColumnTypes(KOSTENReplace,{{"Attribute", Int64.Type}}), Merged = Table.NestedJoin(KOSTENJAHRType, {"Attribute"}, KOSTENType, {"Attribute"}, "KOSTENType", JoinKind.Inner), Expanded = Table.ExpandTableColumn(Merged, "KOSTENType", {"Cost"}, {"Cost"}), Type = Table.TransformColumnTypes(Expanded,{{"Year", Int64.Type}, {"Cost", Currency.Type}}) in Type

Best regards from Germany
Manuel Bolz

🟦Follow me on LinkedIn
🟨How to Get Your Question Answered Quickly
🟩Fabric Community Conference
🟪My Solutions on Github

Re: Need help to manipulate this table correctly

Goatsu — Wed, 12 Jun 2024 09:16:33 GMT

Hello i get the following error:

Expression.Error: The number is outside the valid range of a 32-bit integer value.
Details:
8,5

Re: Need help to manipulate this table correctly

Goatsu — Wed, 12 Jun 2024 09:18:44 GMT

Hello, works - **bleep** you are good

lets suppose depending on the database extract I do i can have Kostenaufteilung and Kostenaufteilung pro Jahr up to 100. Kostenaufteilung 1..2, 3,....100 How can i scale the dimensions infinitely without blowing up the code?

Re: Need help to manipulate this table correctly

ManuelBolz — Wed, 12 Jun 2024 09:24:10 GMT

Hello Goatsu,

I have made my code scalable, so no further adjustments should be necessary. However, it is actually not recommended to import a table with 1.000+ columns and only one row.

If my post helped you, please give me a 👍kudos and mark this post with Accept as Solution.

Best regards from Germany
Manuel Bolz

🟦Follow me on LinkedIn
🟨How to Get Your Question Answered Quickly
🟩Fabric Community Conference
🟪My Solutions on Github

Re: Need help to manipulate this table correctly

Goatsu — Wed, 12 Jun 2024 09:32:35 GMT

Yes, i know that this is suboptimal. However, this is how i get it out from the database 😞

Thank you so much for helping me. Would it mind you if I comment on this post again next week (since i am on vaccation already). Trying it out with more dimensions and "VergabeIDs"? 🙂

Cheers

Re: Need help to manipulate this table correctly

ManuelBolz — Wed, 12 Jun 2024 09:38:36 GMT

Of course you can get in touch again next week. Which database is it? Maybe the data can be pre-transformed using SQL?

Best regards from Germany
Manuel Bolz

🟦Follow me on LinkedIn
🟨How to Get Your Question Answered Quickly
🟩Fabric Community Conference
🟪My Solutions on Github

Re: Need help to manipulate this table correctly

Goatsu — Wed, 12 Jun 2024 09:42:13 GMT

Na sadly not SQL. It is called ELO Datenbank

Re: Need help to manipulate this table correctly

ManuelBolz — Wed, 12 Jun 2024 09:53:46 GMT

Okay,

as long as the current one we have found a solution. However, as soon as the system becomes “too slow” you should think about another solution. As far as I know, “ELO” has either a Microsoft SQL or PostgreSQL database in the backend. So maybe we can already work with SLQ here. Another option would be to retrieve the data using dataflows. I would generally always recommend this option.

I wish you a nice vacation
Manuel Bolz

🟦Follow me on LinkedIn
🟨How to Get Your Question Answered Quickly
🟩Fabric Community Conference
🟪My Solutions on Github

Re: Need help to manipulate this table correctly

AlienSx — Wed, 12 Jun 2024 10:08:32 GMT

Goatsu my code works as long as your data start with Vergabe ID and Kurzbezeichnung (only 2 columns) and equal number of Kostenjahraufteilung and Kostenaufteilung columns. Looks like you have 19 columns in your dataset.

Re: Need help to manipulate this table correctly

dufoq3 — Wed, 12 Jun 2024 11:23:06 GMT

Hi Goatsu, another solution here:

you can edit _ColumnsForGrouping_ step if you have more columns which you want to group by. Change order if you want to have different order in final table.

Result

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LcnBDcAgDATBXvxOJINtTGpB9N9GHDav0d6tJV273aoul5hmFrU4BAz4vwnPwRRa0UKpg4FDwICE+bH3Cw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Kurzbezeichnung = _t, VergabeID = _t, KOSTENJAHRAUFTEILUNG1 = _t, KOSTENJAHRAUFTEILUNG2 = _t, KOSTENJAHRAUFTEILUNG3 = _t, KOSTENJAHRAUFTEILUNG4 = _t, KOSTENJAHRAUFTEILUNG5 = _t, KOSTENJAHRAUFTEILUNG6 = _t, KOSTENJAHRAUFTEILUNG7 = _t, KOSTENJAHRAUFTEILUNG8 = _t, KOSTENAUFTEILUNG1 = _t, KOSTENAUFTEILUNG2 = _t, KOSTENAUFTEILUNG3 = _t, KOSTENAUFTEILUNG4 = _t, KOSTENAUFTEILUNG5 = _t, KOSTENAUFTEILUNG6 = _t, KOSTENAUFTEILUNG7 = _t, KOSTENAUFTEILUNG8 = _t]), _ColumnsForGrouping_ = {"VergabeID", "Kurzbezeichnung"}, fn_TransformToColumns = (myTable as table)=> let // Detail = GroupedRows{0}[All], Detail = myTable, ColNames = List.Buffer(Table.ColumnNames(Detail)), Transform = List.TransformMany( Table.ToRows(Detail), each List.Split(List.Skip(_, List.Count(_ColumnsForGrouping_)), List.Count(List.Select(ColNames, (w)=> Text.StartsWith(w, "KOSTENJAHR", Comparer.OrdinalIgnoreCase)))), (x,y)=> y ), ToTable = Table.FromColumns(Transform, {"Year", "Cost"}) in ToTable, GroupedRows = Table.Group(Source, _ColumnsForGrouping_, {{"All", each fn_TransformToColumns(_), type table}}), ExpandedAll = Table.ExpandTableColumn(GroupedRows, "All", {"Year", "Cost"}, {"Year", "Cost"}) in ExpandedAll

Re: Need help to manipulate this table correctly

Goatsu — Mon, 17 Jun 2024 06:45:49 GMT

Thank you works too. Just got back from vaccation 🙂

Which sources would you recommend in order to learn power query? Have you got a recommendation for me? 🙂

Re: Need help to manipulate this table correctly

Goatsu — Mon, 17 Jun 2024 06:46:59 GMT

Ty 🙂 Do you have btw any recommendation on where to learn advanced power query stuff that you did? Best regards

Re: Need help to manipulate this table correctly

dufoq3 — Mon, 17 Jun 2024 06:56:53 GMT

This one is good.

Re: Need help to manipulate this table correctly

ManuelBolz — Tue, 18 Jun 2024 11:25:18 GMT

Hello Goatsu,

I can highly recommend this book: "The Definitive Guide to Power Query (M)"
Authors: Greg_Deckler, Rickmaurinus and m_dekorte

Best regards from Germany
Manuel Bolz

🟦Follow me on LinkedIn
🟨How to Get Your Question Answered Quickly
🟩Fabric Community Conference
🟪My Solutions on Github