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, 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?

Need Help

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

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

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