Forum Discussion
Transforming data days/weeknumbers
Hi all, i am facing a strange error i cant seem to fix. I have a project planning which contains the following 4 columns; 1. Project name 2. Project responsible 3. Start date 4. End date ...
Please provide some sample data and the code from the Advanced Editor.
---
Hereby some information
| Project name | Responsible | Start date | End date |
| Building storage | Peter | 1-1-2023 | 14-2-2023 |
| Sharepoint creation | Philip | 8-9-2023 | 12-12-2023 |
| AVG rules review/training | Jack | 24-11-2022 | 2-4-2023 |
| Maintenance location 214 | Roger | 1-10-2022 | 6-6-2023 |
let
Bron = SharePoint.Tables("https://.sharepoint.com/sites/Portfolioplanning/", [ApiVersion = 15]),
#"08924eb3-3830-4f09-8656-28e1be54d2d8" = Bron{[Id="08924eb3-3830-4f09-8656-28e1be54d2d8"]}[Items],
#"Namen van kolommen gewijzigd" = Table.RenameColumns(#"08924eb3-3830-4f09-8656-28e1be54d2d8",{{"ID", "ID.1"}}),
#"Rijen gefilterd" = Table.SelectRows(#"Namen van kolommen gewijzigd", each ([Voortgang] <> 100)),
#"Kolommen verwijderd" = Table.RemoveColumns(#"Rijen gefilterd",{"FileSystemObjectType", "Id", "ServerRedirectedEmbedUri", "ServerRedirectedEmbedUrl", "ID.1", "ContentTypeId", "Modified", "AuthorId", "EditorId", "OData__UIVersionString", "Attachments", "GUID", "ComplianceAssetId"}),
#"Teamlid5 uitgevouwen" = Table.ExpandRecordColumn(#"Kolommen verwijderd", "Teamlid5", {"FirstName", "LastName"}, {"Teamlid5.FirstName", "Teamlid5.LastName"}),
#"Kolommen samengevoegd" = Table.CombineColumns(#"Teamlid5 uitgevouwen",{"Teamlid5.FirstName", "Teamlid5.LastName"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Teamlid 5 naam"),
#"Teamlid4 uitgevouwen" = Table.ExpandRecordColumn(#"Kolommen samengevoegd", "Teamlid4", {"FirstName", "LastName"}, {"Teamlid4.FirstName", "Teamlid4.LastName"}),
#"Teamlid3 uitgevouwen" = Table.ExpandRecordColumn(#"Teamlid4 uitgevouwen", "Teamlid3", {"FirstName", "LastName"}, {"Teamlid3.FirstName", "Teamlid3.LastName"}),
#"Teamlid2 uitgevouwen" = Table.ExpandRecordColumn(#"Teamlid3 uitgevouwen", "Teamlid2", {"FirstName", "LastName"}, {"Teamlid2.FirstName", "Teamlid2.LastName"}),
#"Teamlid1 uitgevouwen" = Table.ExpandRecordColumn(#"Teamlid2 uitgevouwen", "Teamlid1", {"FirstName", "LastName"}, {"Teamlid1.FirstName", "Teamlid1.LastName"}),
#"Projectcoordinator uitgevouwen" = Table.ExpandRecordColumn(#"Teamlid1 uitgevouwen", "Projectcoordinator", {"FirstName", "LastName"}, {"Projectcoordinator.FirstName", "Projectcoordinator.LastName"}),
#"Kolommen samengevoegd1" = Table.CombineColumns(#"Projectcoordinator uitgevouwen",{"Projectcoordinator.FirstName", "Projectcoordinator.LastName"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Projectcoordinator naam"),
#"Kolommen samengevoegd2" = Table.CombineColumns(#"Kolommen samengevoegd1",{"Teamlid1.FirstName", "Teamlid1.LastName"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Teamlid 1 naam"),
#"Kolommen samengevoegd3" = Table.CombineColumns(#"Kolommen samengevoegd2",{"Teamlid2.FirstName", "Teamlid2.LastName"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Teamlid 2 naam"),
#"Kolommen samengevoegd4" = Table.CombineColumns(#"Kolommen samengevoegd3",{"Teamlid3.FirstName", "Teamlid3.LastName"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Teamlid 3 naam"),
#"Kolommen samengevoegd5" = Table.CombineColumns(#"Kolommen samengevoegd4",{"Teamlid4.FirstName", "Teamlid4.LastName"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Teamlid 4 naam"),
#"Kolommen verwijderd1" = Table.RemoveColumns(#"Kolommen samengevoegd5",{"ParentList", "Properties", "Versions", "Author", "Editor", "FieldValuesAsHtml", "FieldValuesAsText", "FieldValuesForEdit", "File", "Folder", "LikedByInformation", "RoleAssignments", "AttachmentFiles", "ContentType", "GetDlpPolicyTip", "FirstUniqueAncestorSecurableObject", "Teamlid1Id", "Teamlid1StringId", "Teamlid2Id", "Teamlid2StringId", "Teamlid3Id", "Teamlid3StringId", "Teamlid4Id", "ProjectcoordinatorId", "ProjectcoordinatorStringId", "Teamlid4StringId", "Teamlid5Id", "Teamlid5StringId", "Project", "OData__ColorTag"}),
#"Rijen gefilterd1" = Table.SelectRows(#"Kolommen verwijderd1", each ([Actief] = true)),
#"Type gewijzigd" = Table.TransformColumnTypes(#"Rijen gefilterd1",{{"Begindatum", type date}, {"Einddatum", type date}})
in
#"Type gewijzigd"
I also have tried changing the source to excel, i am getting the same error.
let Bron = Excel.Workbook(File.Contents("C:\Users\TEVUG\OneDrive\Portfolioplanning.xlsx"), null, true), Tabel_query_Table = Bron{[Item="Tabel_query",Kind="Table"]}[Data], #"Type gewijzigd" = Table.TransformColumnTypes(Tabel_query_Table,{{"Title", type text}, {"Projectcoordinator", type text}, {"Teamlid 1", type text}, {"Teamlid 2", type text}, {"Teamlid 3", type text}, {"Teamlid 4", type text}, {"Teamlid 5", type text}, {"Begindatum", type date}, {"Einddatum", type date}, {"Voortgang", Int64.Type}, {"Impact", type text}, {"Prioriteit", type text}, {"Actief", type logical}, {"Project", type any}, {"Item Type", type text}, {"Path", type text}}), #"Aangepaste kolom toegevoegd" = Table.AddColumn(#"Type gewijzigd", "Weekdatum", each List.Dates ([Begindatum], Duration.Days ([Einddatum] - [Begindatum]), #duration (1, 0, 0, 0))), #"Weekdatum uitgevouwen" = Table.ExpandListColumn(#"Aangepaste kolom toegevoegd", "Weekdatum"), #"Week van jaar ingevoegd" = Table.AddColumn(#"Weekdatum uitgevouwen", "Week van jaar", each Date.WeekOfYear([Weekdatum]), Int64.Type), #"Type gewijzigd1" = Table.TransformColumnTypes(#"Week van jaar ingevoegd",{{"Weekdatum", type date}, {"Voortgang", Percentage.Type}}), #"Kolommen samengevoegd" = Table.CombineColumns(Table.TransformColumnTypes(#"Type gewijzigd1", {{"Week van jaar", type text}}, "nl-NL"),{"Title", "Week van jaar"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Samengevoegd week") in #"Kolommen samengevoegd"I have tried to do this in Excel Power Query but that is giving the same error...