Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Consecutive values on multiple columns

Hello,   I hope you can help me with a case I have spent hours on. I am struggling with consecutive values where they are spread over several columns. You can access the sample data via thi...
Need Help
  • slorin's avatar
    slorin
    2 years ago

    Hi,

    another solution

     

    let
    Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
    DatesList = List.Buffer(Source[Date]), 
    Table = #table(
          List.Skip(Table.ColumnNames(Source)),
          {List.Transform(
             List.Skip(Table.ToColumns(Source)),
             each try DatesList{Text.PositionOf(
                 Text.Combine(List.Transform(_,Text.From)),
                 Text.Repeat("2",12))}
             otherwise "--")}
           )
    in
    Table

    Stéphane 

slorin's avatar
slorin
Super User
2 years ago

Hi,

another solution

 

let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
DatesList = List.Buffer(Source[Date]), 
Table = #table(
      List.Skip(Table.ColumnNames(Source)),
      {List.Transform(
         List.Skip(Table.ToColumns(Source)),
         each try DatesList{Text.PositionOf(
             Text.Combine(List.Transform(_,Text.From)),
             Text.Repeat("2",12))}
         otherwise "--")}
       )
in
Table

Stéphane 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

thanks slorin !

I have made minor adjustments and it works as intended in just 2 steps.