Forum Discussion
Transpose table with multiple entries in same category
Hello guys,
I am trying to transpose a dataset which has multiple entries in the same category (in this case "month"). This is the data I prepared with Power Query:
I would like to create a matrix that looks like this:
| Jan | Feb | Mrz | Apr | Mai | Jun | Jul | Aug | ||
| produktive MA | Anwesenheit_Stunden | 1076,483 | 1127,8 | ||||||
| GK-Löhner | Anwesenheit_Stunden | 240,067 | 162,283 | ||||||
| produktive MA | Krank (mit EFZ)_Stunden | 23,25 | 87,75 | ||||||
| GK-Löhner | Krank (mit EFZ)_Stunden | 50,75 | 148 | ||||||
| produktive MA | etc. | ||||||||
| GK-Löhner |
Do you have any tips for me?
Hi wuumbs ,
Please try like:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZE9C8IwEIb/imSuYC79sGMXhWrBvXSoWLRUWgnWwV9v3+KQg7slJLknT/Lm6tpYE5myHZfx4qfbPLz7T7epimWNis3+EzJNFMLH0/Y8PcbOr7WF2TMQW4fuKlgduFyBQ2uM+o6BOFv5r2BNwFgFDq0p6sRAXFS8vGBFeHIKHFoRnmIG4kVV2wtWhKdEgUOrRXpKGYlVOYvtWqFMoZkX+Yk3LFvJp+TFB1Cu0MyLH3C8ZbilmO+SF6GdVWjmRQwXNK35AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Monat = _t, Monat_Name = _t, Mitarbeiter = _t, Anwesenheit_Stunden = _t, #"Krank (mit EFZ)_Stunden" = _t, Column1 = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Monat", Int64.Type}, {"Monat_Name", type text}, {"Mitarbeiter", type text}, {"Anwesenheit_Stunden", Int64.Type}, {"Krank (mit EFZ)_Stunden", Int64.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Monat", "Column1", "Column2"}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Monat_Name", "Mitarbeiter"}, "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Monat_Name]), "Monat_Name", "Value", List.Sum) in #"Pivoted Column"Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
1 Reply
Hi wuumbs ,
Please try like:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZE9C8IwEIb/imSuYC79sGMXhWrBvXSoWLRUWgnWwV9v3+KQg7slJLknT/Lm6tpYE5myHZfx4qfbPLz7T7epimWNis3+EzJNFMLH0/Y8PcbOr7WF2TMQW4fuKlgduFyBQ2uM+o6BOFv5r2BNwFgFDq0p6sRAXFS8vGBFeHIKHFoRnmIG4kVV2wtWhKdEgUOrRXpKGYlVOYvtWqFMoZkX+Yk3LFvJp+TFB1Cu0MyLH3C8ZbilmO+SF6GdVWjmRQwXNK35AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Monat = _t, Monat_Name = _t, Mitarbeiter = _t, Anwesenheit_Stunden = _t, #"Krank (mit EFZ)_Stunden" = _t, Column1 = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Monat", Int64.Type}, {"Monat_Name", type text}, {"Mitarbeiter", type text}, {"Anwesenheit_Stunden", Int64.Type}, {"Krank (mit EFZ)_Stunden", Int64.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Monat", "Column1", "Column2"}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Monat_Name", "Mitarbeiter"}, "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Monat_Name]), "Monat_Name", "Value", List.Sum) in #"Pivoted Column"Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data