Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How to make dates table to show all dates in range

Hello

 

I have the following table in Excel and i would like to create a complete dates table from 1 January 2022 until 31 December 2022 by individual days (as rows) which shows all the names (as columns):

 

Name
Lizui
Laufenburg
Tegalpapak
Ar Rabiyah
Bellegarde
Gangarampur
Luntas
Frei Paulo
Seedorf
Cosamaloapan de Carpio
Zagrodno

 

Then, use the range of dates from the table below into that new table (for example, put a number '1' in the corresponding name-date cells and '0' in the other cells). How to do this? Any help is much appreciated!!

 

NameFromUntil
Lizui11/01/202221/01/2022
Laufenburg02/01/202229/01/2022
Tegalpapak22/01/202226/02/2022
Ar Rabiyah05/02/202202/03/2022
Bellegarde07/02/202214/03/2022
Gangarampur17/03/202223/05/2022
  • If I understand what you want correctly, so far as the orientation of the report, the code below should do that. 

    For each name:

    • List.Transform changes are list of allDates into a 1 or 0 depending on whether it is in the range of From-Until
    • List.Generate creates a List of the List Transformations corresponding to each name
    • Use Table.FromColumns to convert these lists into the result table, using the Names in the Name column as the column headers.

    In the screenshot, the dates are in MDY format but, since they are "real" dates, they will  display in your native format on your machine.

     

     

     

    let
    
    //Read in the filter table
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc2xCoMwEIDhV5HMQpKzKo516dKpdBOHE6OGRgmBDPXpvSBEMx7/d3ddx95695rlTEouJAcBQANcQ5+TQT+pbfBupibgDpsEftWMxqLFX2gJrDgtRvh02QcH/cclXCxjO88XF2yVMXTUjSq0+gblI4Ev3Ijhar0LsY6RfhecXpyyPwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, From = _t, Until = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{
            {"Name", type text}, 
            {"From", type date}, 
            {"Until", type date}}, "en-150"),
    
    //create lists with dates 1 or 0 corresponding to each Name/From/Until
        allDates = List.Buffer(List.Dates(#date(2022,1,1),365,#duration(1,0,0,0))),
        dateCols = List.Generate(
                ()=>[d=List.Transform(allDates, 
                            (L)=> if L >=#"Changed Type"[From]{0} and L <=#"Changed Type"[Until]{0} then 1 else 0),
                            idx=0],
                each [idx]
                            if L >=#"Changed Type"[From]{[idx]+1} and L <= #"Changed Type"[Until]{[idx]+1} then 1 else 0),
                            idx=[idx]+1],
                each [d]),
    
    //create the table and set the data types
        result= Table.FromColumns({allDates} & dateCols, {"Date"} & #"Changed Type"[Name]),
    
        typeIt = Table.TransformColumnTypes(result, {{"Date", type date}} &
           List.Transform(List.RemoveFirstN(Table.ColumnNames(result),1), each {_, Int64.Type})
    )   
    in 
        typeIt

     

     

     

    ronrsnfld_0-1644631578342.png

     

     

11 Replies

  • If I understand what you want correctly, so far as the orientation of the report, the code below should do that. 

    For each name:

    • List.Transform changes are list of allDates into a 1 or 0 depending on whether it is in the range of From-Until
    • List.Generate creates a List of the List Transformations corresponding to each name
    • Use Table.FromColumns to convert these lists into the result table, using the Names in the Name column as the column headers.

    In the screenshot, the dates are in MDY format but, since they are "real" dates, they will  display in your native format on your machine.

     

     

     

    let
    
    //Read in the filter table
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc2xCoMwEIDhV5HMQpKzKo516dKpdBOHE6OGRgmBDPXpvSBEMx7/d3ddx95695rlTEouJAcBQANcQ5+TQT+pbfBupibgDpsEftWMxqLFX2gJrDgtRvh02QcH/cclXCxjO88XF2yVMXTUjSq0+gblI4Ev3Ijhar0LsY6RfhecXpyyPwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, From = _t, Until = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{
            {"Name", type text}, 
            {"From", type date}, 
            {"Until", type date}}, "en-150"),
    
    //create lists with dates 1 or 0 corresponding to each Name/From/Until
        allDates = List.Buffer(List.Dates(#date(2022,1,1),365,#duration(1,0,0,0))),
        dateCols = List.Generate(
                ()=>[d=List.Transform(allDates, 
                            (L)=> if L >=#"Changed Type"[From]{0} and L <=#"Changed Type"[Until]{0} then 1 else 0),
                            idx=0],
                each [idx]
                            if L >=#"Changed Type"[From]{[idx]+1} and L <= #"Changed Type"[Until]{[idx]+1} then 1 else 0),
                            idx=[idx]+1],
                each [d]),
    
    //create the table and set the data types
        result= Table.FromColumns({allDates} & dateCols, {"Date"} & #"Changed Type"[Name]),
    
        typeIt = Table.TransformColumnTypes(result, {{"Date", type date}} &
           List.Transform(List.RemoveFirstN(Table.ColumnNames(result),1), each {_, Int64.Type})
    )   
    in 
        typeIt

     

     

     

    ronrsnfld_0-1644631578342.png

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi ronrsnfld Thank you so much for your reply!

       

      But there is an error if there are more than one similar name in the table, as below. How to fix this?

       

      NameFromUntil
      Lizui11/01/202221/01/2022
      Laufenburg02/01/202229/01/2022
      Tegalpapak22/01/202226/02/2022
      Ar Rabiyah05/02/202202/03/2022
      Bellegarde07/02/202214/03/2022
      Gangarampur17/03/202223/05/2022
      Tegalpapak22/02/202228/02/2022
      Lizui11/02/202221/02/2022
      Bellegarde03/01/202214/02/2022
      Gangarampur07/01/202213/02/2022

       

      • ronrsnfld's avatar
        ronrsnfld
        Icon for Super User rankSuper User

        "But there is an error if there are more than one similar name in the table, as below. How to fix this?"

         

        Maybe a language issue, but an important one. Those are not similar names; those are identical names.  You can have columns with similar names. However, you may not create a table where column names are identical.  If the two identical names represent two different people, you will need to add something to the names to differentiate them (perhaps an Index number).  If they are the same person, you will need to merge the information so it produces only a single column.

         

        Which is it?

  • Anonymous's avatar
    Anonymous
    Not applicable

    ronrsnfld Thanks! It worked!

     

    So, i have applied the same logic to a bigger table with more columns. Here is the initial code resulting in a table in Power Query with only 3 columns (Name, From, Until):

     

     

     

     

    Then, i added the code as below but there is an error:

     

     

     

     

     

     

    So, i renamed the line:

     

     

    #"Removed Columns" = Table.RemoveColumns(dateTable,{"From", "Until"}),

     

     

    And change it to:

     

     

    #"Removed Columns2" = Table.RemoveColumns(dateTable,{"From", "Until"}),

     

     

    In Advanced Editor, it says, no syntax errors have been detected. But then i see this error in Power Query:

    I'm not sure what went wrong. 

    • ronrsnfld's avatar
      ronrsnfld
      Icon for Super User rankSuper User

      First you need to figure out which line is producing the error.

      I suggest you select each step in Applied Steps, starting at the last step and working up, until you determine that.

      Then, most likely, you have an incorrect reference to a table, possibly related to your re-naming.

       

      By the way,

         Your code would be a lot shorter (and easier to understand) if you only

      • Set the data type for the columns that are not going to be deleted
        • Also, set your date columns to type date, not type datetime
      • Use Table.SelectColumns instead of Table.RemoveColumns