Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Goatsu
Frequent Visitor

Need help to manipulate this table correctly

Hello dear community,

 

My database extract looks like this:

 

Goatsu_0-1718180201537.png

 

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:

 

Goatsu_2-1718180458790.png

 

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

 

Thank you all in advance!

 

Cheers

 

2 ACCEPTED SOLUTIONS
ManuelBolz
Responsive Resident
Responsive Resident

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

View solution in original post

dufoq3
Super User
Super User

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.

dufoq3_0-1718191246879.png

 

Result

dufoq3_1-1718191361807.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

15 REPLIES 15
dufoq3
Super User
Super User

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.

dufoq3_0-1718191246879.png

 

Result

dufoq3_1-1718191361807.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Goatsu
Frequent Visitor

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

This one is good.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

ManuelBolz
Responsive Resident
Responsive Resident

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

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
Responsive Resident
Responsive Resident

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

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
Responsive Resident
Responsive Resident

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

Na sadly not SQL. It is called ELO Datenbank

ManuelBolz
Responsive Resident
Responsive Resident

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

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

ManuelBolz
Responsive Resident
Responsive Resident
AlienSx
Super User
Super User

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

Hello i get the following error:

 

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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors