Forum Discussion
Transforming data days/weeknumbers
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
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"})