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 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!
 
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.

2 Replies

  • 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.