Transforming Data in Power Query

Hi Shalom,

Result:

2 versions of code here. V1 is similar with AlienSx version. If I were you I would go for v2 because it is dynamic (no matter how many colum pairs do you have), but maybe you have to adjust v2_Extracted Text Before Delimiter because I don't know if you have space in every column name (as you have in example Dep 1, Dep 2 etc...). Of course it is possible to make dynamic v1 but I need to know your real column names.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLIz00FUiERQMLUysCgIBfICC4tKMjJTC0GMn0dgYSJlaEpRCIxByzqDBI1NLIyBqmP1YlWMkKSdIwCEuZWJhAtfo7BIMX+HsgWuGXmJeYlwy02hIiDDDJGkQXbYw7T5QhyFYo+QysjiJR3ZklyRmoeyD4XIGEMcXBsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, #"Dep 1" = _t, #"State 1" = _t, #"Time 1" = _t, #"Dep 2" = _t, #"State 2" = _t, #"Time 2" = _t, #"Dep 3" = _t, #"State 3" = _t, #"Time 3" = _t]),
    v1_ListTransformMany = List.TransformMany(List.Buffer(Table.ToRows(Source)),
     each List.Split(List.Skip(_), 3),
     (x,y)=> {x{0}} & y
  ),
    v1_ToTable = Table.FromRows(v1_ListTransformMany, {"Item", "Dep", "State", "Time"}),
    StepBack = Source,
    #"v2_Unpivoted Other Columns" = Table.UnpivotOtherColumns(StepBack, {"Item"}, "Column Name", "Value"),
    #"v2_Extracted Text Before Delimiter" = Table.TransformColumns(#"v2_Unpivoted Other Columns", {{"Column Name", each Text.BeforeDelimiter(_, " "), type text}}),
    v2_GroupedRows = Table.Group(#"v2_Extracted Text Before Delimiter", {"Column Name"}, {{"All", each _, type table}, {"List", each Table.ToColumns(Table.SelectColumns(_, List.Select(Table.ColumnNames(_), each _ <> "Column Name"))), type list}}),
    v2_Zip = List.Zip(v2_GroupedRows[List]),
    v2_ToTable = Table.FromColumns({v2_Zip{0}{0}} & List.Skip(v2_Zip){0}, {"Item"} & v2_GroupedRows[Column Name])
in
    v2_ToTable