Forum Discussion
Help converting DAX column into Power Query
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: ComplaintsDAX 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.
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]
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.
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.
