Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Power BI Power Query edits not being applied

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, ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
lbendlin
Super User
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    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.