Power BI & UnPivot Data

Hi BTV

Is this the output you want, based on the date can order the row.

If this is the output you want. you can refer to the following solution.

1.Put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7ZNRS8MwEMe/ytHnQddUcfq6ihRhyFbxYQw523MLtJeapEK/vWmoyLo+uPkyYW+5O/7/u+SXW6+DNAkmQRRsJusgQUsuEFMRh7MwmvnkXDVsdevyzyufWNJWKnbxPRrrM6umrssWatJQKbY744oZGQvOEO8gU/BGgEVBBSiGNIleo6Eu/rVSeGnK0kosj+op4gPpEW3jq/72uW6krYitEy3po5Gair5k5JbRutfpDBf+hKWvZRolS94CcgEJfVKp6oEHSAa7I3iX2k3SzdU/Up6TMc4KnkrkzqSb1o9YEZrmu70HKQ5A3oRiOgA5R8YC92G+0IkwxckwxckwxV9gin8DMx7byusBzAfSFXK7T3OhtN39GI2u9+1wvR8v633uP2IMpBj+iAvI8wW5+QI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Attribute = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute", type text}, {"Value", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "ID", each if [Attribute] = "ID" then [Value] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Date", each if [Attribute] = "Date" then [Value] else null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Country", each if [Attribute] = "Country" then [Value] else null),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Region", each if [Attribute] = "Region" then [Value] else null),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom3",{{"ID", Int64.Type}, {"Date", type date}}),
    #"Filled Down" = Table.FillDown(#"Changed Type1",{"ID"}),
    #"Added Index" = Table.AddIndexColumn(#"Filled Down", "Index", 1, 1, Int64.Type),
    #"Sorted Rows" = Table.Sort(#"Added Index",{{"ID", Order.Ascending}})
in
    #"Sorted Rows"

2.Then create three calculated column

Dates = var a=MAXX(FILTER('Table',[ID]=EARLIER('Table'[ID])&&[Index]<EARLIER('Table'[Index])&&[Attribute]<>"ID"&&[Attribute]="Date"),[Index])
var b=LOOKUPVALUE('Table'[Date],'Table'[Index],a)
return SWITCH([Attribute],"ID",LOOKUPVALUE('Table'[Date],'Table'[Index],[Index]+1),"Date",[Date],b)

Countries = var a=MAXX(FILTER('Table',[ID]=EARLIER('Table'[ID])&&[Index]<EARLIER('Table'[Index])&&[Attribute]<>"ID"&&[Attribute]="Country"),[Index])
var b=LOOKUPVALUE('Table'[Country],[Index],a)
var c=MINX(FILTER('Table',[ID]=EARLIER('Table'[ID])&&[Index]>=EARLIER('Table'[Index])&&[Attribute]<>"ID"&&[Attribute]="Country"),[Index])
return IF([Attribute]="ID"||[Attribute]="Date"||[Attribute]="Country",LOOKUPVALUE('Table'[Country],'Table'[Index],c),b)

Regions = var a=MAXX(FILTER('Table',[ID]=EARLIER('Table'[ID])&&[Index]<EARLIER('Table'[Index])&&[Attribute]<>"ID"&&[Attribute]="Region"),[Index])
var b=LOOKUPVALUE('Table'[Region],[Index],a)
var c=MINX(FILTER('Table',[ID]=EARLIER('Table'[ID])&&[Index]>=EARLIER('Table'[Index])&&[Attribute]<>"ID"&&[Attribute]="Region"),[Index])
return IF([Attribute]="ID"||[Attribute]="Date"||[Attribute]="Country"||[Attribute]="Region",LOOKUPVALUE('Table'[Region],'Table'[Index],c),b)

Then you need to create a table visual and put the following field to the visual

 

Then you can click shift+ left click to sort the visual. you can refer to the following link.

How to sort by multiple columns in Power BI [Table Visual Examples] - SPGuides

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.