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 data like this every week for every day.
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"
4 Replies
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"
