Forum Discussion

Goatsu

Frequent Visitor

2 years ago

Solved

Need help to manipulate this table correctly

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

Need Help

ManuelBolz

2 years ago

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

2 years ago

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

15 Replies

AlienSx
Super User
2 years ago
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
  Frequent Visitor
  2 years ago
  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
    Super User
    2 years ago
    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.

ManuelBolz

Responsive Resident

2 years ago

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
Frequent Visitor
2 years ago
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
  2 years ago
  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

dufoq3

Community Champion

2 years ago

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
Frequent Visitor
2 years ago
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
  Community Champion
  2 years ago
  This one is good.

Forum Discussion

Need help to manipulate this table correctly

15 Replies

Related content

Stored Procedure to Manipulate Tables

Table Visual Manipulation

Manipulate Data

Table Not Totalling Correctly

table doesnt sum correctly