PBI requests all tables switched to import mode when unpivoting columns

[jutu]

Hi. I have 2 columns that I need them both combined into one column as below.
In this case it works as it should when I unpivot ZoneW and ZoneS columns, but I am quite puzzeled because in a different report (quite a large report) it does work but it asks that it's switched all tables to Import mode which I tried but then it throws error that some transformations in my report can't be used for Import query mode.
I don't know why it does ask to switch it to Import mode and only way around it that I can think of is to try a different way of combining both columns. Is there another way to action it to have it combined into 1 column instead of two. And do you know why it would ask me to switch it to Import mode. It doesn't usually do and I have no idea why in this occassion it does for not apparent reason. Thank you

Please note that I'm unpivoting just 2 columns in the actual table, not 3 columns as below, but I suppose it would work similarly when unpivoting 2 or 3 columns

​	​	​	​
Currently like this​	​	​	​
Cust.No​	Zone W.Reply​	Zone T.Reply​	Zone S.Reply​
458232539​	Y​	​	​
62955626S​	​	N​	​
652623520​	​	​	Y​
652623537​	Y​	​	​
65262354S​	​	Y​
652623554			N

Required like this
Cust.No	Reply
45823253W	Y
62955626S	N
652623520	Y
65262353W	Y
65262354S	Y
652623554	N

 

[AlanSidman]

Your customer Numbers do not match up. But a simple unpivot should work. You will have an extra column when you unpivot but you can remove it before close and load.

 

[GraH - Guido]

When it's a large table, it might be better to use this kind of construct:

let
    Source = Excel.CurrentWorkbook(){[Name="sample"]}[Content],
    Add_Record = Table.CombineColumnsToRecord(Source, "Record",{"Zone W.Reply","Zone S.Reply"}),
    Convert_to_table = Table.TransformColumns(Add_Record,{{"Record", Record.ToTable, type table}}),
    Keep_cust_no_rec = Table.SelectColumns(Convert_to_table,{"Cust.No", "Record"}),
    Expand_rec = Table.ExpandTableColumn(Keep_cust_no_rec, "Record", {"Name", "Value"}, {"Zone", "Reply"})
in
    Expand_rec

But the switch to import mode, I guess you are using PowerBI and not Excel and I assume your source data is actually a cube. Switching to import mode means you want to do a transformation on the model and hence it would create a "local" model. It still connects to the cube, but you can change the content of it.

And as far as I know it's not that straightforward when the source model is huge. That can't be loaded locally. So you need to recreate the connection to the cube in Power Query: you need to know the server name and the model. Then add items from the model and select only those you need and remember to filter the required rows only too. Another potential issue here is query folding. When it breaks, it can have a catastrophic impact on the performance of your query.

 

[jutu]

The table didn't match.

 

[jutu]

The table didn't match.

Hey sorry for the above short reply 'The table didn't match'. I posted it but must have deleted the rest of what I meant to post. I meant to reply to AlanSidman sorry