cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KarlinOz
Helper IV
Helper IV

Problem with embedded if statement - M Script

Hi People,

Hoping someone can help me with my script please, I'm probably missing something simple but I have spent two hours on this, searched this forum and wider via Google but haven't found a solution.

 

The part of my script I'm having problems with:

= Table.AddColumn(#"Appended CreditNotes Details", "PO2", each if Text.PositionOf([InvoiceNumber],"<") +1 = 0 
			then null 
			else
				Text.Range([InvoiceNumber],
				Text.PositionOf([InvoiceNumber],"<")+1,
				if Text.PositionOf([InvoiceNumber],">") +1 = 0 
				then 4 
				else Text.PositionOf([InvoiceNumber],">"))
)

 

I am adding a new column which is to extract the text between < and >, exclusive. However, some lines do not have the closing > and in those cases I want to select just the 4 characters after the < character.  My script compiles fine but when I run the query I get an error reported on every row that has an opening < character. 

 

If I run this code:

 

Table.AddColumn(#"Appended CreditNotes Details", "PO2", each if Text.PositionOf([InvoiceNumber],"<") +1 = 0 then null else
            Text.Range([InvoiceNumber],
            Text.PositionOf([InvoiceNumber],"<")+1,
            if Text.PositionOf([InvoiceNumber],">") +1 > 0 then Text.PositionOf([InvoiceNumber],">") else Text.Length([InvoiceNumber])-1-Text.PositionOf([InvoiceNumber],"<"))

 

It runs fine but I get an error for every row that has an < character with no > character. The rows with < and > return what I expect.

 

My whole script:

let
    Source = Sql.Database("acterys.database.windows.net", "AP_MyCompany", [MultiSubnetFailover=true]),
    xero_Invoices = Source{[Schema="xero",Item="Invoices"]}[Data],
    #"Filtered Rows" = Table.SelectRows(xero_Invoices, each [Status] = "Authorised" or [Status] = "Paid" or [Status] = "Draft" and [TrackingCategory1_Option] <> null and not Text.StartsWith([TrackingCategory1_Option], "x1") and [Date] > #datetime(2016, 4, 1, 0, 0, 0)),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"UpdatedDateUTC", Order.Descending}, {"Date", Order.Descending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows", {"AmountDue", "BrandingThemeID", "CurrencyCode", "CurrencyRate", "ExternalLinkProviderName", "HasAttachments", "InvoiceID", "DiscountRate", "ItemCode", "TaxType", "SentToContact", "SubTotal", "Total", "TotalDiscount", "TotalTax", "Type", "OrgShortCode", "OrgName"}),
    #"Added Labour Actual" = Table.AddColumn(#"Removed Columns", "Labour Actual", each if [AccountCode] = "00" or [AccountCode] = "51071" then if [LineAmountTypes]="Inclusive" then [LineAmount]-[TaxAmount] else [LineAmount] else "0"),
    #"Changed Type Labour Actual" = Table.TransformColumnTypes(#"Added Labour Actual",{{"Labour Actual", Currency.Type}, {"CreditNotes", Int64.Type}}),
    #"Added Income" = Table.AddColumn(#"Changed Type Labour Actual", "Income", each if ([AccountCode] = "41000" or [AccountCode] = "41200") then 
	 if [LineAmountTypes]="Inclusive" then 
            [LineAmount]-[TaxAmount] else [LineAmount] else 0),
    #"Sorted Rows1" = Table.Sort(#"Added Income",{{"Income", Order.Ascending}})
,
    #"Changed Type Income" = Table.TransformColumnTypes(#"Sorted Rows1",{{"Income", Currency.Type}}),
    #"Added Subcontractors Labour" = Table.AddColumn(#"Changed Type Income", "Subcontractors Labour", each if [AccountCode] = "51071" then if [LineAmountTypes]="Inclusive" then [LineAmount]-[TaxAmount] else [LineAmount] else 0),
    #"Changed Type Subcontractors Labour" = Table.TransformColumnTypes(#"Added Subcontractors Labour",{{"Subcontractors Labour", Currency.Type}}),
    #"Added AmountType" = Table.AddColumn(#"Changed Type Subcontractors Labour", "AmountType", each if [AccountCode] = "00" or [AccountCode] = "04" or [AccountCode] = "51071" then "Labour" else if  [AccountCode] = "41000" or [AccountCode] = "41200" then "" else "Plant & Materials"),
    #"Added AmountType2" = Table.AddColumn(#"Added AmountType", "AmountType2", each if [AccountCode] = "03" or [AccountCode] = "04" then "Budget" else if [AccountCode] = "41000" or [AccountCode] = "41200" then "Income" else "Actual"),
    #"Added LabourLines" = Table.AddColumn(#"Added AmountType2", "LabourLines", each if [AmountType] = "Labour" then [LineAmount] else "0" ),
    #"Added BudgetLines" = Table.AddColumn(#"Added LabourLines", "BudgetLines", each if [AmountType2] = "Budget" then [LineAmount] else "0" ),
    #"Added ActualLines" = Table.AddColumn(#"Added BudgetLines", "ActualLines", each if [AmountType2] = "Actual" then if [LineAmountTypes]="Inclusive" then [LineAmount]-[TaxAmount] else [LineAmount] else "0"),
    #"Added Index" = Table.AddIndexColumn(#"Added ActualLines", "Index", 1, 1),
    #"Appended CreditNotes Details" = Table.Combine({#"Added Index", CreditNotesDetails}),
    #"Added PO Column" =Table.AddColumn(#"Appended CreditNotes Details", "PO2", each if Text.PositionOf([InvoiceNumber],"<") +1 = 0 
			then null 
			else
				Text.Range([InvoiceNumber],
				Text.PositionOf([InvoiceNumber],"<")+1,
				if Text.PositionOf([InvoiceNumber],">") +1 = 0 
				then 4 
				else Text.PositionOf([InvoiceNumber],">"))
),
    #"Appended logistics_BPR_invoiced" = Table.Combine({#"Added PO Column", logistics_BPR_invoiced_costs})
in
    #"Appended logistics_BPR_invoiced"
1 ACCEPTED SOLUTION
Mariusz
Community Champion
Community Champion

Hi @KarlinOz 

 

Try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVogpNTAwTlZITUuHMFMVKiqrlGJ1opUqKipgskCFKUAVGWBxIEMpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each 
        let 
            start = Text.PositionOf( [Column1], "<" ),
            endsc = Text.PositionOf( [Column1], ">" ),
            end = if endsc = -1 then 4 else endsc - start -1,
            select = 
                if start >= 0 then Text.Range( [Column1], start +1, end ) else null
        in 
            select, type text
    )
in
    #"Added Custom"
Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski



View solution in original post

4 REPLIES 4
Mariusz
Community Champion
Community Champion

Hi @KarlinOz 

 

Try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVogpNTAwTlZITUuHMFMVKiqrlGJ1opUqKipgskCFKUAVGWBxIEMpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each 
        let 
            start = Text.PositionOf( [Column1], "<" ),
            endsc = Text.PositionOf( [Column1], ">" ),
            end = if endsc = -1 then 4 else endsc - start -1,
            select = 
                if start >= 0 then Text.Range( [Column1], start +1, end ) else null
        in 
            select, type text
    )
in
    #"Added Custom"
Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski



View solution in original post

Hi @Mariusz ,

Thanks for that, you are spot on. I had a feeling I would need another 'let' in there but wasn't sure how to construct it. You have also shown me how to use variables, thanks for that too.

For completeness my final solution looked like this:

= Table.AddColumn(#"Appended CreditNotes Details", "PO2", each 
        let 
            start = Text.PositionOf( [InvoiceNumber], "<" ),
            endsc = Text.PositionOf( [InvoiceNumber], ">" ),
            end = if endsc = -1 then 4 else endsc - start -1,
            select = 
                if start >= 0 then Text.Range( [InvoiceNumber], start +1, end ) else null
        in 
            select, type text
    )

The whole thing:

let
    Source = Sql.Database("acterys.database.windows.net", "AP_MyCompany", [MultiSubnetFailover=true]),
    xero_Invoices = Source{[Schema="xero",Item="Invoices"]}[Data],
    #"Filtered Rows" = Table.SelectRows(xero_Invoices, each [Status] = "Authorised" or [Status] = "Paid" or [Status] = "Draft" and [TrackingCategory1_Option] <> null and not Text.StartsWith([TrackingCategory1_Option], "x1") and [Date] > #datetime(2016, 4, 1, 0, 0, 0)),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"UpdatedDateUTC", Order.Descending}, {"Date", Order.Descending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows", {"AmountDue", "BrandingThemeID", "CurrencyCode", "CurrencyRate", "ExternalLinkProviderName", "HasAttachments", "InvoiceID", "DiscountRate", "ItemCode", "TaxType", "SentToContact", "SubTotal", "Total", "TotalDiscount", "TotalTax", "Type", "OrgShortCode", "OrgName"}),
    #"Added Labour Actual" = Table.AddColumn(#"Removed Columns", "Labour Actual", each if [AccountCode] = "00" or [AccountCode] = "51071" then if [LineAmountTypes]="Inclusive" then [LineAmount]-[TaxAmount] else [LineAmount] else "0"),
    #"Changed Type Labour Actual" = Table.TransformColumnTypes(#"Added Labour Actual",{{"Labour Actual", Currency.Type}, {"CreditNotes", Int64.Type}}),
    #"Added Income" = Table.AddColumn(#"Changed Type Labour Actual", "Income", each if ([AccountCode] = "41000" or [AccountCode] = "41200") then 
	 if [LineAmountTypes]="Inclusive" then 
            [LineAmount]-[TaxAmount] else [LineAmount] else 0),
    #"Sorted Rows1" = Table.Sort(#"Added Income",{{"Income", Order.Ascending}})
,
    #"Changed Type Income" = Table.TransformColumnTypes(#"Sorted Rows1",{{"Income", Currency.Type}}),
    #"Added Subcontractors Labour" = Table.AddColumn(#"Changed Type Income", "Subcontractors Labour", each if [AccountCode] = "51071" then if [LineAmountTypes]="Inclusive" then [LineAmount]-[TaxAmount] else [LineAmount] else 0),
    #"Changed Type Subcontractors Labour" = Table.TransformColumnTypes(#"Added Subcontractors Labour",{{"Subcontractors Labour", Currency.Type}}),
    #"Added AmountType" = Table.AddColumn(#"Changed Type Subcontractors Labour", "AmountType", each if [AccountCode] = "00" or [AccountCode] = "04" or [AccountCode] = "51071" then "Labour" else if  [AccountCode] = "41000" or [AccountCode] = "41200" then "" else "Plant & Materials"),
    #"Added AmountType2" = Table.AddColumn(#"Added AmountType", "AmountType2", each if [AccountCode] = "03" or [AccountCode] = "04" then "Budget" else if [AccountCode] = "41000" or [AccountCode] = "41200" then "Income" else "Actual"),
    #"Added LabourLines" = Table.AddColumn(#"Added AmountType2", "LabourLines", each if [AmountType] = "Labour" then [LineAmount] else "0" ),
    #"Added BudgetLines" = Table.AddColumn(#"Added LabourLines", "BudgetLines", each if [AmountType2] = "Budget" then [LineAmount] else "0" ),
    #"Added ActualLines" = Table.AddColumn(#"Added BudgetLines", "ActualLines", each if [AmountType2] = "Actual" then if [LineAmountTypes]="Inclusive" then [LineAmount]-[TaxAmount] else [LineAmount] else "0"),
    #"Added Index" = Table.AddIndexColumn(#"Added ActualLines", "Index", 1, 1),
    #"Appended CreditNotes Details" = Table.Combine({#"Added Index", CreditNotesDetails}),
    #"Added PO Column" = Table.AddColumn(#"Appended CreditNotes Details", "PO2", each 
        let 
            start = Text.PositionOf( [InvoiceNumber], "<" ),
            endsc = Text.PositionOf( [InvoiceNumber], ">" ),
            end = if endsc = -1 then 4 else endsc - start -1,
            select = 
                if start >= 0 then Text.Range( [InvoiceNumber], start +1, end ) else null
        in 
            select, type text
    ),
    #"Appended logistics_BPR_invoiced" = Table.Combine({#"Added PO Column", logistics_BPR_invoiced_costs})
in
    #"Appended logistics_BPR_invoiced"
Mariusz
Community Champion
Community Champion

Hi @KarlinOz 

 

I'm glad to help.

 

Best Regards,
Mariusz

Please feel free to connect with me.
Mariusz Repczynski

 

Mariusz
Community Champion
Community Champion

Hi @KarlinOz 

 

Can you provide a data sample?

 

Best Regards,
Mariusz

Please feel free to connect with me.
Mariusz Repczynski

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.