Power BI Power Query edits not being applied

[Anonymous]

Hi, I have been having issues with my power query changes being applied. When I click the close and apply button in the editor, it will start to save, but it will stop during the process. Typically, it will stop when loading the rows from my SQL server import. It will reach around 300,000 rows from the database, and stop, then never complete the application of my changes.

while the table being imported has lots of data, I am only trying to use a very small portion of it, and in my power query I trim the table down to only 1000 rows. Any advice for fixing this?

[lbendlin]

needs more details - is this on Power BI desktop?  Is it a Dataflow?  Have you recently made meta data changes?

[Anonymous]

Hi, sorry about the late response, This is on the desktop app, it is a relativly simple transformation on a table using the following script

let
Source = Sql.Database("connectwise.database.windows.net", "ConnectWise"),
dbo_CwAuditTrail = Source{[Schema="dbo",Item="CwAuditTrail"]}[Data],
#"Removed Columns" = Table.RemoveColumns(dbo_CwAuditTrail,{"enteredBy"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([newStatus] <> null)),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each [auditText]),
#"Extracted Text Between Delimiters" = Table.TransformColumns(#"Added Custom", {{"Custom", each Text.BetweenDelimiters(_, """", """"), type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Extracted Text Between Delimiters",{{"Custom", "Old Status"}, {"newStatus", "New Status"}, {"enteredDate", "Entered Date"}, {"auditText", "Audit Text"}, {"TicketId", "Ticket Id"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Entered Date", type datetime}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type",{{"Ticket Id", "Ticket ID"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns1",{{"Ticket ID", Order.Descending}, {"Entered Date", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
#"Added Custom2" = Table.AddColumn(#"Added Index", "Duration", each if [Index]=0 then null else if [Ticket ID]=#"Sorted Rows"[Ticket ID]{[Index]-1} then [Entered Date]-#"Sorted Rows"[Entered Date]{[Index]-1} else null),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"Duration", type duration}})
in
#"Changed Type1"

That is really the only thing I have done in power BI

[lbendlin]

The sorting and the pointer math are likely what's killing it. 

I have recently seen a clever approach - after sorting, make a copy of the table containing only the entered date, then add a zero based index to one table and a one based index to the other.  When you merge the tables back together you get the pointer math for free.  Might want to try that.

[Anonymous]

I've confirmed it is at the very least not the sorting, as if I load the step before the pointer math, it load fairly quickly. I will see what I can do about trying out the method you gave me with the math and see if that helps. However, I don't believe this will fix the main issue. Currently, when I try to apply the changes, Power BI will not finish loading the database, and will eventually get stuck here:

Except it will be around the 300,000 mark.

Edit, it looks like that was wrong, as when I tried deleting the section that does the pointer math, it successfully applied the changes.