Forum Discussion

DemoFour's avatar
DemoFour
Continued Contributor
2 years ago

Strange error - PQ Online in a dataflow

Hi peeps, 

 

I have a dataflow that was working, and is now throwing a strange error! 

 

This is in a dimension table where I build out what I need for the model. 

Prefix is a field in the source table, I have checked the source file, it is there.  I have checked in PQ and it is in the source table.  

I have no idea why this is now an issue, I have rebuilt and got the same error, I have brought the column through and it still happens.  The query is a simple remove unwanted columns, remove duplicates, sort rows A-Z and then add an index.  

 

 

let
  Source = #"Event Attendees Tidy",
  #"Choose columns" = Table.SelectColumns(Source, {"Business Name", "Which Local Authority area is your business registered in?", "How is your business registered?", "What is the approximate annual turnover of your business? (If not trading, put £0)", "Business Sector"}),
  #"Removed duplicates" = Table.Distinct(#"Choose columns", {"Business Name", "Business Sector"}),
  #"Sorted rows" = Table.Sort(#"Removed duplicates", {{"Business Name", Order.Ascending}}),
  #"Added index" = Table.AddIndexColumn(#"Sorted rows", "Business ID", 0, 1, Int64.Type),
  #"Reordered columns" = Table.ReorderColumns(#"Added index", {"Business ID", "Business Name", "Which Local Authority area is your business registered in?", "How is your business registered?", "What is the approximate annual turnover of your business? (If not trading, put £0)", "Business Sector"})
in
  #"Reordered columns"

 



The query falls over at the sort rows stage! 

Any help please on debugging this would be much appreciated.

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi DemoFour ,

     

    The steps in Power Query are sequential. The current step inherits the relationship of the previous step. So you can check if the previous step of your error step has a column about "Prefix". I'm guessing it's because you didn't have the Prefix column in the previous step, but the step you reported an error with covered the "Prefix" column, but you couldn't find the column and reported an error.

     

    Applied steps - Power Query | Microsoft Learn

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

  • DemoFour's avatar
    DemoFour
    Continued Contributor
    2 years ago

    Hi Anonymous 

    The field Prefix is in the source table, this is then removed in the Choose Columns step. 

    I understand the sequencial step process, I have tried to bring the Prefix field in with the other columns and still get the error. 

    I have also built out in PBI Desktop PQ and not had any issues, so it is a bit confusing. 

    Thank you for your reply, I spent a few hrs yesterday debugging but can not fix the issue.