Forum Discussion
Sort column Values in Power BI
Hi,
I am new to Power BI.
I have one requirement to sort 'QTR_Yr' column values in a table. For that I have created one table with 'Qtr_Yr' column from the main table and added one more column 'Id '. In 'Qtr_Yr' column, I have values like below:
RST Q3'17
RST Q2'17
RST Q3'17
RST FY'17
ACT + LE P02 Q2'18
ACT + LE P02 Q2'18
ACT + LE P02 Q4'18
ACT + LE P03 Q2'18
ACT + LE P05 Q2'18
Plan Q2'18
Plan Q3'18
Plan FY'19
Plan FY'20
I want to sort this values. But I couldn't find any pattern to sort. So added new table with Id. To calculate 'Id' column, I am writting below condition:
qtr_yr_id =
SWITCH (
TRUE (),
'Qtr_Yr_Sort'[Quarter Year] = "RST Q1'17", "1", 'Qtr_Yr_Sort'[Quarter Year] = "RST Q2'17", "2", 'Qtr_Yr_Sort'[Quarter Year] =
'Qtr_Yr_Sort'[Quarter Year] = "ACT + LE P03 Q2'18", "3",'Qtr_Yr_Sort'[Quarter Year] = "ACT + LE P03 Q3'18", = "Plan Q3'18", "4", 'Qtr_Yr_Sort'[Quarter Year] = "Plan Q4'18","5", 'Qtr_Yr_Sort'[Quarter Year] = "Plan FY'18", "6", 'Qtr_Yr_Sort'[Quarter Year] = "Plan FY'19", "14", 'Qtr_Yr_Sort'[Quarter Year] = "Plan FY'20", "15","16")
To compare above values which are in single quote '"RST Q1'17", How I should compare this in Power BI?
When I tried to replace single quote values as """, I am getting error. Can anyone please help me on this?
Also, by looking at the above pattern of values, Is there any other way to sort these values instead of creating new table and manually adding ids in new column to sort?
Thanks in advance!
Regards,
Poonam
Hi Anonymous,
Could you have tried add the index column in query editor?
Reference:https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi
Regards,
Daniel He
3 Replies
Hi Anonymous,
Could you have tried add the index column in query editor?
Reference:https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi
Regards,
Daniel He
Hi v-danhe-msft,
Thanks for the reply. But I want to sort this column like below:
1st all RST related and Quarterly sorted values and then sorted by Period,
then ACT (Actuals) related values,
then Plan related. Something like below :
RST Q2'17
RST Q3'17
RST FY'17
ACT + LE P02 Q2'18
ACT + LE P02 Q3'18
ACT + LE P02 Q4'18
Plan Q2'18
Plan Q3'18
Plan FY'19
I could find any pattern to sort this values. So I tried writting 'Switch' statement. Added above formula.
But the 'Quarter Year' has 'Single quate' (') in it. I am not able to compare the value.
example,
qtr_yr_id =
SWITCH (
TRUE (),
'Qtr_Yr_Sort'[Quarter Year] = "RST Q2'17", "1", 'Qtr_Yr_Sort'[Quarter Year] = "RST Q3'17", "2", "3")I am getting "3" value in the "qtr_yr_id " column. What should i write to compare the valaue when it has Single quote (') in it.
Thanks!
Regards,
Poonam
Hi v-danhe-msft,
Actually the issue was different. The values which I mentioned above has special characters in it. eg. 'RST Q3'17' has special character in it. so I am not able to compare it with Qtr_Yr column. But thanks for the suggestion. Adding index is a good idea if we enter the data in the column manually.
Thanks!
Regards,
Poonam