Apply query changes getting stuck (small dataset)

[DCELL]

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.

[DCELL · Accepted 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 😃

[mahoneypat]

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]

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]

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!

[lbendlin]

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]

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]

run that same query in SSMS and check its performance.