cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KarlinOz
Advocate III
Advocate III

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



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
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors