Forum Discussion

baaaailey's avatar
baaaailey
Frequent Visitor
3 years ago
Solved

Help converting DAX column into Power Query

Hi All - I created some DAX custom columns to later use reference tables for a bridge, without realizing that they could not be used in Power Query. I am not as familiar with PQ yet as I am with DAX ...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    3 years ago

    Hi, baaaailey ;

    Table 1: vw_Fact_SalesOrderLine

    DAX 1: add custom column

     

    =Text.Combine(List.Select(Text.Split([Sales order ID]," "), each _<>"")," ")

     

    DAX 2:  

     

    =if Text.Middle([TrimmedSalesOrderId],0,5) ="1900-" then Text.AfterDelimiter([TrimmedSalesOrderId], "1900-")
else if Text.Middle([TrimmedSalesOrderId],0,3) ="SO0" 
then [TrimmedSalesOrderId]
else "SO0"&[TrimmedSalesOrderId]

     

    DAX 3:

     

    =[SalesOrderId]&"-"&[Item ID]

     

    Table 2: Complaints
    DAX 1: 
    if Text.Middle([SO__c],0,2) ="00" then 
    "SO0"& Text.Middle([SO__c],2,99999) 
else if  Text.Middle([SO__c],0,4) ="SO00" then
      "SO0"& Text.Middle([SO__c],4,99999)
else if Text.Middle([SO__c],0,3) <>"SO0" then
      "SO0"& [SO__c]
      else [SO__c]


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-yalanwu-msft's avatar
v-yalanwu-msft
Community Support
3 years ago

Hi, baaaailey ;

Table 1: vw_Fact_SalesOrderLine

DAX 1: add custom column

 

=Text.Combine(List.Select(Text.Split([Sales order ID]," "), each _<>"")," ")

 

DAX 2:  

 

=if Text.Middle([TrimmedSalesOrderId],0,5) ="1900-" then Text.AfterDelimiter([TrimmedSalesOrderId], "1900-")
else if Text.Middle([TrimmedSalesOrderId],0,3) ="SO0" 
then [TrimmedSalesOrderId]
else "SO0"&[TrimmedSalesOrderId]

 

DAX 3:

 

=[SalesOrderId]&"-"&[Item ID]

 

Table 2: Complaints
DAX 1: 
if Text.Middle([SO__c],0,2) ="00" then 
    "SO0"& Text.Middle([SO__c],2,99999) 
else if  Text.Middle([SO__c],0,4) ="SO00" then
      "SO0"& Text.Middle([SO__c],4,99999)
else if Text.Middle([SO__c],0,3) <>"SO0" then
      "SO0"& [SO__c]
      else [SO__c]


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

baaaailey's avatar
baaaailey
Frequent Visitor
3 years ago

Thank you very much! With your help in the previous conversions from DAX to power query, I tried to do the last 2 DAX columns from the Complaints table, but am getting an error.

if 
 Text.Middle([CleanSalesOrderId],0,4) ="SO00" 
  then 
   "SO0"& Text.Middle([CleanSalesOrderId],4,"SO00") 
else 
  if  
   Text.Middle([CleanSalesOrderId],0,6) ="SO0SO0" 
    then
     "SO0"& Text.Middle([CleanSalesOrderId],6,"SO0SO0")
       else 
 [CleanSalesOrderId]

 

Expression.Error: We cannot convert the value "SO00" to type Number.
Details:
Value=SO00
Type=[Type]

 

 

Can you see why I am getting this error? The column is of type Text.