Help converting DAX column into Power Query

[baaaailey]

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 syntax, and could use some help with transferring them over to power query formulas. I have 2 tables with 3 DAX columns within each table that I used to clean-up some of the data and format.

Here is the first table's DAX Columns:

Table 1: vw_Fact_SalesOrderLine

DAX 1: TrimmedSalesOrderId = TRIM(vw_Fact_SalesOrderLine[Sales order ID])

DAX 2:  

SalesOrderId = IF(LEFT(vw_Fact_SalesOrderLine[TrimmedSalesOrderId],5)="1900-",REPLACE(vw_Fact_SalesOrderLine[TrimmedSalesOrderId],1,5,""),IF(LEFT(vw_Fact_SalesOrderLine[TrimmedSalesOrderId],3)<>"SO0","SO0"&vw_Fact_SalesOrderLine[TrimmedSalesOrderId],vw_Fact_SalesOrderLine[TrimmedSalesOrderId]))

DAX 3:

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

Table 2: Complaints

DAX 1: 

CleanSalesOrderId = IF(LEFT(Complaints[SO__c],2)="00",REPLACE(Complaints[SO__c],1,2,"SO0"),IF(LEFT(Complaints[SO__c],4)="SO00",REPLACE(Complaints[SO__c],1,4,"SO0"),IF(LEFT(Complaints[SO__c],3)<>"SO0","SO0"&Complaints[SO__c],Complaints[SO__c])))

DAX 2: 

SalesOrderIdKey = if(left(Complaints[CleanSalesOrderId],4)="SO00",REPLACE(Complaints[CleanSalesOrderId],1,4,"SO0"),if(left(Complaints[CleanSalesOrderId],6)="SO0SO0",REPLACE(Complaints[CleanSalesOrderId],1,6,"SO0"),Complaints[CleanSalesOrderId]))

DAX 3:

SalesOrderItemKey = IF(Complaints[Product_Name__c]="MULTIPLE", Complaints[SalesOrderIdKey],if(Complaints[Product_Name__c]<>"",Complaints[SalesOrderIdKey]&"-"&Complaints[Product_Name__c],Complaints[SalesOrderIdKey]))

Please let me know if you need any other information that I'm missing to help me with this, Much Appreciated!

[v-yalanwu-msft · Accepted solution]

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]

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.