Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Count number of columns depending on inserted format

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

Need Help
  • ImkeF's avatar
    ImkeF
    3 years ago

    Hi Anonymous ,
    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"

     

2 Replies

  • ImkeF's avatar
    ImkeF
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi Anonymous ,
    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"

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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