Forum Discussion
Rikats
2 years agoRegular Visitor
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...
- 2 years ago
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"
Greg_Deckler
Community Champion
2 years agoRikats 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
2 years agoRegular Visitor
OMG, perfect, worked like a charm, thanks 🙂