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...
im working with confidential data so i have to change alot to get you a sample..
