Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Solved! Go to Solution.
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"
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"
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |