Need help to manipulate this table correctly

[Goatsu]

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

[ManuelBolz · Accepted solution]

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

[dufoq3 · Accepted solution]

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

[Goatsu]

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? 🙂

[dufoq3]

This one is good.

[ManuelBolz · Accepted solution]

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

[Goatsu]

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?

[ManuelBolz]

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

[Goatsu]

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

[ManuelBolz]

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

[Goatsu]

Na sadly not SQL. It is called ELO Datenbank

[ManuelBolz]

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

[Goatsu]

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

[ManuelBolz]

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

[AlienSx]

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

[Goatsu]

Hello i get the following error:

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

[AlienSx]

@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.