Count number of columns depending on inserted format

[ClementdW]

Good evening community !

I need some help with a report I am trying to build. The report I am working on aims to monitor planned & realised visits made by my employees.

I have a column for each month of the year (therefore, 12 columns).

- when an employee plans to make a visit a certain month, he enters a text value."VISIT X" (X being 1, 2, 3...) is often used, but note that they may use other text.

- when an employee comes back from a visit, he changes this value with the date of the visit. Format: DD/MM/YYYY.

What I would like to do in Power Query is to create:

- a custom column which calculates, for each row, the number of planned visits (therefore number of columns containing text value)

- a custom column which calculates, for each row, the number of realised visits (therefore number of columns containing date value)

Even if they do not always use the same text or date format, I can use the number of characters to calculate this if needed.

Thanks for the support !

Clément

[ClementdW]

Hello @ImkeF 

Thank you very much for this !

I just added a List.Range to select specific columns in my data model, and it works fine.

Thanks again,

Clément

[ImkeF · Accepted solution]

Hi @ClementdW ,
please paste the code below into the advanced editor of a new query and follow the steps to examine the logic:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText("i45WCvMM9gxR0lFSitWJVjIw1AUiIwMjI6AIRCY2FgA=", BinaryEncoding.Base64), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Column1 = _t, Column2 = _t]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"Column1", type text}, {"Column2", type text}}
  ), 
  #"Added Custom" = Table.AddColumn(
    #"Changed Type", 
    "realized visits", 
    each List.Count(
      List.Select(
        List.Transform(Record.FieldValues(_), (l) => try Date.From(l) otherwise null), 
        (lo) => lo <> null
      )
    )
  ), 
  #"Added Custom1" = Table.AddColumn(
    #"Added Custom", 
    "planned visits", 
    each List.Count(
      List.Select(
        List.Transform(Record.FieldValues(_), (l) => try Text.Length(l) otherwise null), 
        (lo) => lo > 0
      )
    )
      - [realized visits]
  )
in
  #"Added Custom1"