Transforming data days/weeknumbers

[Teddibeer]

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

I am trying to create a row for each week which falls under the start/end date.

e.g. start date 1-1-2023, end date 31-1-2023; i want to see 5 rows with weeknumber 1 upto 5.

As soon as i implement these steps in the transforming data i get the following error;

OLE DB- of ODBC-fout: [Expression.Error] Het argument count valt buiten het bereik..

How do i fix this so i get the outcome i want?

In transform data i get the result i want but as soon as i press apply that error pops up.

Kind regards,

Teddy

[HotChilli]

Have you followed my suggestions as per last post?

1. Load all data in preview and look for error

or

2. cut back the steps in Power Query and see if you can load all the data (close and apply) to see if it works. Then gradually re-introduce steps to see if it breaks

[Teddibeer]

i did and nothing worked unfortunately

[HotChilli]

The initial post suggested that the algorithm used to split data into rows-per-date is failing once the full dataset is processed (Preview is limited to 1000 rows normally) but the code provided shows a Sharepoint connection and just expanding and combining various columns i.e. no specific algorithm to get rows from 2 dates.

--

So my question is, is the error coming from an expansion of a column from sharepoint (there are many documented challenges working with Sharepoint data in powerbi)?

If I was testing this, I would create a test pbix and load all rows in the Preview (it's an option at the bottom of the Power Query page)

If that breaks, it should identify which step needs looked at (or indicates a problem with the data).
--

Alternatively, cut back the steps in Power Query and see if you can load all the data (close and apply) to see if it works.  Then gradually re-introduce steps to see if it breaks

[Teddibeer]

I have added the code below which causes the error and i still get the same error as before.

    #"Type gewijzigd" = Table.TransformColumnTypes(#"Kolommen verwijderd1",{{"Begindatum", type date}, {"Einddatum", type date}}),
    #"Aangepaste kolom toegevoegd" = Table.AddColumn(#"Type gewijzigd", "weekdatum2", each List.Dates ( [Begindatum] , Duration.Days ([Einddatum] - [Begindatum]), #duration (1, 0, 0, 0))),
    #"weekdatum2 uitgevouwen" = Table.ExpandListColumn(#"Aangepaste kolom toegevoegd", "weekdatum2"),
    #"Week van jaar ingevoegd" = Table.AddColumn(#"weekdatum2 uitgevouwen", "Week van jaar", each Date.WeekOfYear([weekdatum2]), Int64.Type),
    #"Kolommen samengevoegd6" = Table.CombineColumns(Table.TransformColumnTypes(#"Week van jaar ingevoegd", {{"Week van jaar", type text}}, "nl-NL"),{"Title", "Week van jaar"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"SamengevoegdWeek"),
    #"Dubbele waarden verwijderd" = Table.Distinct(#"Kolommen samengevoegd6", {"SamengevoegdWeek"})

[HotChilli]

I don't see any connection between the sample code and the data provided (apart from the last line which changes data type)

[HotChilli]

Please provide some sample data and the code from the Advanced Editor.

---

[Teddibeer]

im working with confidential data so i have to change alot to get you a sample..

[Teddibeer]

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"

[Teddibeer]

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...