Three dimensional table into one dimensional

[Rikats]

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. 

[Greg_Deckler]

@Rikats Can you post the source data as text/table?

[Rikats]

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

[Greg_Deckler · Accepted solution]

@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]

OMG, perfect, worked like a charm, thanks 🙂