Split and transform columns into rows

nok Please try with this PQ script:

(change the text YOURPATH, YOURBOOKNAME and SHEETNAME of the first two steps accordling to your file path, excelbookname and sheetname) 

let
  Source = Excel.Workbook(File.Contents("YOURPATH\YOURBOOKNAME.xlsx"), null, true), 
  SheetWithData = Source{[Item = "SHEETNAME", Kind = "Sheet"]}[Data], 
  #"Filtered Rows" = Table.SelectRows(SheetWithData, each [Column1] <> null and [Column1] <> ""), 
  #"Transposed Table" = Table.Transpose(#"Filtered Rows"), 
  #"Trimmed Text" = Table.TransformColumns(#"Transposed Table", {{"Column1", Text.Trim, type text}}), 
  #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text", {{"Column1", Text.Clean, type text}}), 
  #"Replaced Value" = Table.ReplaceValue(
    #"Cleaned Text", 
    "[", 
    "", 
    Replacer.ReplaceText, 
    {"Column2"}
  ), 
  #"Replaced Value1" = Table.ReplaceValue(
    #"Replaced Value", 
    "]", 
    "", 
    Replacer.ReplaceText, 
    {"Column2"}
  ), 
  #"Changed Type" = Table.TransformColumnTypes(
    #"Replaced Value1", 
    {{"Column2", type date}, {"Column3", Int64.Type}}
  ), 
  #"Pivoted Column" = Table.Pivot(
    #"Changed Type", 
    List.Distinct(#"Changed Type"[Column1]), 
    "Column1", 
    "Column3", 
    List.Sum
  )
in