Forum Discussion
Solved
Three dimensional table into one dimensional
Hello! I new with PowerBI and DAX and i can't figure out how to get data like this from excel to PowerBI. Could you please give tips how to do automaticaly with DAX - Power Query. I get d...
Rikats How about this?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVXSUTIw1AMiIwMjY4IcI+I4xoQ4sTrRSgpAIbf8/BQg5VKUmZcNolOLU4tKSBYHmeaVn5EHFHHOyCwoBtLhiSWpRUDaMxnkQ6fSonQw17y0AC6IrtQlPy8RYlZAKkQErs05PycRrs8tsziDaKO8gEEMkglOzElMQZgEUUCiWcGJZYl5eYlIarCbhuJa3Mb55SdmICkILijKLEklz7RYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t]), #"Transposed Table" = Table.Transpose(Source), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type date}, {" ", type text}, {"John", type text}, {"Peter", type text}, {"James", type text}, {"Savanna", type text}, {"Noah", type text}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{" ", "Type"}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Name", "Type"}, "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Type]), "Type", "Value"), #"Renamed Columns1" = Table.RenameColumns(#"Pivoted Column",{{"Name", "Date"}, {"Attribute", "Name"}}) in #"Renamed Columns1"
Rikats Can you post the source data as text/table?
Sure, 🙂
Unmerged first row due to restriction.
| Name | 01.01.2023 | 01.01.2023 | 01.01.2023 | 01.02.2023 | 01.02.2023 | 01.02.2023 | 01.03.2023 | 01.03.2023 | 01.03.2023 |
| Food | Drink | Desert | Food | Drink | Desert | Food | Drink | Desert | |
| John | Chips | Water | Ice | Burger | 7up | Ice | Chips | Water | Donat |
| Peter | Burger | Cola | Ice | Fish | 7up | Ice | Chips | Water | Donat |
| James | Salad | Cola | Donat | Fish | 7up | Ice | Chips | Water | Donat |
| Savanna | Chips | Cola | Donat | Fish | Cola | Ice | Chips | Water | Donat |
| Noah | Chips | Sprite | Donat | Fish | Cola | Ice | Chips | Water | Donat |
Rikats How about this?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVXSUTIw1AMiIwMjY4IcI+I4xoQ4sTrRSgpAIbf8/BQg5VKUmZcNolOLU4tKSBYHmeaVn5EHFHHOyCwoBtLhiSWpRUDaMxnkQ6fSonQw17y0AC6IrtQlPy8RYlZAKkQErs05PycRrs8tsziDaKO8gEEMkglOzElMQZgEUUCiWcGJZYl5eYlIarCbhuJa3Mb55SdmICkILijKLEklz7RYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t]), #"Transposed Table" = Table.Transpose(Source), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type date}, {" ", type text}, {"John", type text}, {"Peter", type text}, {"James", type text}, {"Savanna", type text}, {"Noah", type text}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{" ", "Type"}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Name", "Type"}, "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Type]), "Type", "Value"), #"Renamed Columns1" = Table.RenameColumns(#"Pivoted Column",{{"Name", "Date"}, {"Attribute", "Name"}}) in #"Renamed Columns1"- OMG, perfect, worked like a charm, thanks 🙂