Excel BI PQ Challenge #107

excelbipqchallenge107

let

 Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 

 #"Added Coalesce" = Table.AddColumn(Source, "Coalesce", each [Column2] ?? [Column3] ?? [Column1]), 

 #"Filtered Rows" = Table.SelectRows(#"Added Coalesce", each ([Coalesce] <> null)), 

 #"Added Date" = Table.AddColumn(

  #"Filtered Rows", 

  "Date", 

  each try Date.From([Column1]) otherwise null, 

  type date

 ), 

 #"Added Index" = Table.AddIndexColumn(#"Added Date", "Index", 0, 1, Int64.Type), 

 #"Inserted Integer-Division" = Table.AddColumn(

  #"Added Index", 

  "Integer-Division", 

  each Number.IntegerDivide([Index], 6), 

  Int64.Type

 ), 

 #"Removed Other Columns" = Table.SelectColumns(

  #"Inserted Integer-Division", 

  {"Coalesce", "Date", "Integer-Division"}

 ), 

 #"Grouped Rows" = Table.Group(

  #"Removed Other Columns", 

  {"Integer-Division"}, 

  {

   {"Dates", each List.Max([Date]), type date}, 

   {"All", each _, type table [Coalesce = any, Date = date, #"Integer-Division" = number]}

  }

 ), 

 #"Build Output List" = Table.AddColumn(

  #"Grouped Rows", 

  "Output", 

  each List.Combine(

   {List.Range([All][Coalesce], 2, 2), {[All][Coalesce]{4} + [All][Coalesce]{5}}}

  )

 ), 

 #"Removed Other Columns1" = Table.SelectColumns(#"Build Output List", {"Dates", "Output"}), 

 #"Extracted Values" = Table.TransformColumns(

  #"Removed Other Columns1", 

  {"Output", each Text.Combine(List.Transform(_, Text.From), ","), type text}

 ), 

 #"Split Column by Delimiter" = Table.SplitColumn(

  #"Extracted Values", 

  "Output", 

  Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), 

  {"PO Number", "Supplier", "PO Value"}

 )

in

 #"Split Column by Delimiter"