Raw Data Transforming

Lobs 

In Power Query, you can select all the columns from Client up to Cluster and right-click, select UnPivot Other Columns, you will the desired layout.

Paste the following code in  Blank query and check

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcswpyEhU0lEKcAQS7qWJZSCOd2oekASJEECxOtFKTqklID3uIOXOGalFRZVARlBlKZEmxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Client = _t, Campus = _t, Program = _t, DPE = _t, #"CLUSTER " = _t, #"NOV 1 6PM" = _t, #"NOV 1 8PM" = _t, #"NOV 1 10PM" = _t, #"NOV 1 11PM" = _t, #"NOV 2 1AM" = _t, #"01-11-02 3:00" = _t, #"NOV 2 4AM" = _t, #"NOV 2 6AM" = _t, #"NOV 2 8AM" = _t, #"NOV 2 10AM" = _t, #"NOV 2 12PM" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Client", "Campus", "Program", "DPE", "CLUSTER "}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

I guess you are seeing blank because you dont have data 

I am not sure whether you want blanks in the final output, but you can try like this

let
    Source = Excel.Workbook(File.Contents("C:\Users\...\Sample.xlsx"), null, true),
    Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet2_Sheet, [PromoteAllScalars=true]),
    #"Replaced Value" = Table.ReplaceValue(#"Promoted Headers",null,"zzz",Replacer.ReplaceValue,{"NOV 1 6PM", "NOV 1 8PM", "NOV 1 10PM", "NOV 1 11PM", "NOV 2 1AM", "NOV 2 3 AM", "NOV 2 4AM", "NOV 2 6AM", "NOV 2 8AM", "NOV 2 10AM", "NOV 2 12PM"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Client", "Campus", "Program", "DPE", "CLUSTER "}, "Attribute", "Value"),
    #"Replaced Value1" = Table.ReplaceValue(#"Unpivoted Other Columns","zzz","",Replacer.ReplaceText,{"Value"})
in
    #"Replaced Value1"

you see the output as