Solved: Re: Problem with embedded if statement - M Script

KarlinOz · ‎10-24-2019

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"

Mariusz · ‎10-25-2019

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.

View solution in original post

Mariusz · ‎10-25-2019

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.

KarlinOz · ‎10-26-2019

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"