Forum Discussion

DCELL's avatar
DCELL
Resolver I
6 years ago
Solved

Apply query changes getting stuck (small dataset)

I have a small dataset (1115 rows, 5 columns) imported from SSMS.

All my query changes have been fine so far, but when I try to add a simply conditional column and try to apply, it will get stuck here

 
 
 
 
 
 

The steps are not complicated either:

Sorted by date then by category, renamed 2 of the columns, then added this conditional column with 1 'if' condition and an 'else' 'other' specification. The conditional column step makes it hang.

  • Well guys - yesterday it didn't work and today it did. I still don't know why, maybe the old pc needed a reboot. Thanks for your replies anyhow.

     

    If someone runs into the same problem in the future I guess they could try a reboot first and then see if it works 😃

7 Replies

  • When you say SSMS do you mean SQL Server?
    Where are you trying to add the column, in Power Query or in DAX?

    Does your query support query folding or is that "Native Query"  option greyed out?

    • DCELL's avatar
      DCELL
      Resolver I

      Yes, SQL Server.
      I'm trying to add it in Power Query, I don't know of a way to add it in DAX.

      I didn't specify an SQL query in the advanced options when I imported. I can view the native query and see the full query fold and the lines of data where it put in the conditional column with a CASE statement:

       

      select [_].[Yr] as [Yr],
          [_].[MonthNo] as [MonthNo],
          [_].[amt] as [amt],
          [_].[gltr_domain] as [domain],
          [_].[MonthEnd] as [Date],
          [_].[Currency] as [Currency]
      from 
      (
          select [_].[Yr] as [Yr],
              [_].[MonthNo] as [MonthNo],
              [_].[amt] as [amt],
              [_].[gltr_domain] as [gltr_domain],
              [_].[MonthEnd] as [MonthEnd],
              case
                  when [_].[gltr_domain] = 'usa'
                  then 'USD'
                  else 'Other'
              end as [Currency]
          from 
          (
              select [_].[Yr],
                  [_].[MonthNo],
                  [_].[amt],
                  [_].[gltr_domain],
                  [_].[MonthEnd]
              from [DATABASE].[TABLE] as [_]
          ) as [_]
      ) as [_]
      order by [MonthEnd],
              [gltr_domain]

       

       

      • lbendlin's avatar
        lbendlin
        Super User

        run that same query in SSMS and check its performance.

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee

    Your post says if...else...other.  Just to clarify, do you use if ... then ... else , which is the expected M syntax?

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

    • DCELL's avatar
      DCELL
      Resolver I

      I did not write the M code myself. I only used the dialog box that pops up below. So the code in the native query is just what PBI wrote for me.

       

      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support

        Hi DCELL ,

         

        So your issue is solved ,right?

        If so,could you pls mark the reply as answered to close it?

         

        Best Regards,
        Kelly
        Did I answer your question? Mark my post as a solution!
  • Well guys - yesterday it didn't work and today it did. I still don't know why, maybe the old pc needed a reboot. Thanks for your replies anyhow.

     

    If someone runs into the same problem in the future I guess they could try a reboot first and then see if it works 😃