Forum Discussion
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!!
| Name | From | Until |
| Lizui | 11/01/2022 | 21/01/2022 |
| Laufenburg | 02/01/2022 | 29/01/2022 |
| Tegalpapak | 22/01/2022 | 26/02/2022 |
| Ar Rabiyah | 05/02/2022 | 02/03/2022 |
| Bellegarde | 07/02/2022 | 14/03/2022 |
| Gangarampur | 17/03/2022 | 23/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
11 Replies
- ronrsnfld
Super User
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 - AnonymousNot 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?
Name From Until Lizui 11/01/2022 21/01/2022 Laufenburg 02/01/2022 29/01/2022 Tegalpapak 22/01/2022 26/02/2022 Ar Rabiyah 05/02/2022 02/03/2022 Bellegarde 07/02/2022 14/03/2022 Gangarampur 17/03/2022 23/05/2022 Tegalpapak 22/02/2022 28/02/2022 Lizui 11/02/2022 21/02/2022 Bellegarde 03/01/2022 14/02/2022 Gangarampur 07/01/2022 13/02/2022 - ronrsnfld
Super 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?
- AnonymousNot 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
Super 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
- Set the data type for the columns that are not going to be deleted