locked
trying to concatenate text and number RRS feed

  • Question

  • I am trying to concatenate text column and a number column and the Add column button gave me a answer saying there were no syntax errors but when I look in the column it is full of errors. I have not been using the advanced editor but the various buttons on the Transform or Add Column Tabs and I am confused with some of answers I am seeing in response to questions I am asking online.  I am connecting to an Access Database

    I have the following questions

    1. I checked on this site and found I needed to use Text.From but all the lines of code that have been created for me I have the fields is in square brackets but the examples on this site are not using square brackets.  Why do I have square brackets or is this a quirk of using the function buttons instead of writing the code in the Advanced Editor
    2. Why is the error checker saying no errors when there is no data in the created column
    3. Why does the Advanced Editor have lots of Let clauses but only one In clause when all the examples I see online have there own let and in clause?
    4. This is the row that is not concatenating properly   #"Add Supplier Name and Ref C" = Table.AddColumn(#"Added Conditional Column", "Supplier Name and Ref", each [Supplier] &" "&Text.From[System Ref No]),
    5. Below I have copied the information from the Advanced Editor

    I really would be grateful is someone could explain my questions and also help me with why the concatenation is still not working.

    Many thanks


    let
        Source = Access.Database(File.Contents("xxxxxxxxxxxxxxxxxx.1.0_be.accdb"), [CreateNavigationProperties=true]),
        #"_Core Suppliers" = Source{[Schema="",Item="Core Suppliers"]}[Data],
        #"NC CHAS Expiries" = Table.AddColumn(#"_Core Suppliers", "CHAS Expiries", each if [CHAS Approved]=false then "Not Applicable" else if List.AllTrue({[CHAS Approved]=true,[CHAS Expiry Date]<DateTime.LocalNow()}) then "Chase" else "Insurance OK"),
        #"Changed Type" = Table.TransformColumnTypes(#"NC CHAS Expiries",{{"PI Insurance Expiry Date", type date}, {"Public Liability Expiry Date", type date}}),
        #"NC PI PL EL Insurance" = Table.AddColumn(#"Changed Type", "PI PL Insurance", each if List.AnyTrue({[PI Insurance Copy]=true,[Public Liability Copy]=true,[Employers Liability Copy]=true}) then "Insurance Required" else "Not Applicable"),
        #"Changed Type2" = Table.TransformColumnTypes(#"NC PI PL EL Insurance",{{"Public Liability Expiry Date", type date}}),
        #"Changed Type3" = Table.TransformColumnTypes(#"Changed Type2",{{"Employers Liability Expiry", type date}}),
        #"NC Insurance Status" = Table.AddColumn(#"Changed Type3", "Insurance Status", each if List.AnyTrue({[PI Insurance Expiry Date]<=Date.From(DateTime.LocalNow()),[Public Liability Expiry Date]<=Date.From(DateTime.LocalNow()),[Employers Liability Expiry]<=Date.From(DateTime.LocalNow())}) then "Overdue"  else if List.AllTrue({[PI Insurance Expiry Date] = null,[Public Liability Expiry Date]= null,[Employers Liability Expiry] =  null}) then "Overdue" else "Current"),
        #"Merged Queries" = Table.NestedJoin(#"NC Insurance Status",{"Cat"},tbl_category_of_supply,{"CAT ID"},"tbl_category_of_supply",JoinKind.LeftOuter),
        #"Expanded tbl_category_of_supply" = Table.ExpandTableColumn(#"Merged Queries", "tbl_category_of_supply", {"Risk Score"}, {"tbl_category_of_supply.Risk Score"}),
        #"Added Conditional Column" = Table.AddColumn(#"Expanded tbl_category_of_supply", "Risk Category", each if [tbl_category_of_supply.Risk Score] >= 9 then "High Risk" else if [tbl_category_of_supply.Risk Score] >= 5 then "Medium Risk" else "Low Risk"),
        #"Add Supplier Name and Ref C" = Table.AddColumn(#"Added Conditional Column", "Supplier Name and Ref", each [Supplier] &" "&Text.From[System Ref No]),
        #"Filtered Rows2" = Table.SelectRows(#"Add Supplier Name and Ref C", each true),
        #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows2",{"Supplier Name and Ref"}),
        #"Inserted Merged Column" = Table.AddColumn(#"Removed Columns", "Supplier and Ref", each Text.Combine({[Supplier], Text.From([System Ref No], "en-GB")}, " "), type text),
        #"Inserted Merged Column1" = Table.AddColumn(#"Inserted Merged Column", "Supplier Info", each Text.Combine({[Supplier], Text.From([System Ref No], "en-GB")}, ":"), type text),
        #"Removed Columns1" = Table.RemoveColumns(#"Inserted Merged Column1",{"Supplier and Ref"}),
        #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"tbl_category_of_supply.Risk Score", "Risk Score"}}),
        #"Added Custom2" = Table.AddColumn(#"Renamed Columns", "Custom", each if [Live Supplier] = true and [CHAS Approved] = true then "CHAS" else "Not CHAS"),
        #"Inserted Merged Column2" = Table.AddColumn(#"Added Custom2", "Risk Type", each Text.Combine({[Custom], [Risk Category]}, " "), type text),
        #"Renamed Columns1" = Table.RenameColumns(#"Inserted Merged Column2",{{"Custom", "CHAS?"}}),
        #"Added Custom4" = Table.AddColumn(#"Renamed Columns1", "SSIP", each if([Live Supplier]=true and [CHAS Approved]=true) then "SSIP" else if([Live Supplier]=true and [SMAS]=true) then "SSIP" else if([Live Supplier]=true and [SAFECONTRACTOR]=true) then "SSIP" else if ([Live Supplier]=false) then "No longer a Supplier" else "non SSIP"),
        #"Filtered Rows" = Table.SelectRows(#"Added Custom4", each true),
        #"Inserted Merged Column3" = Table.AddColumn(#"Filtered Rows", "SSIP Risk", each Text.Combine({[SSIP], [Risk Category]}, " "), type text),
        #"Renamed Columns2" = Table.RenameColumns(#"Inserted Merged Column3",{{"SSIP", "SSIP?"}}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Created Date", type date}, {"CHAS Expiry Date", type date}, {"Last Quality Audit Date", type date}, {"Last H&S Audit Date", type date}, {"Last Environmental Audit Date", type date}, {"Last Procurement Audit Date", type date}, {"STANDARD EXPIRY DATE", type date}}),
        #"Added Custom5" = Table.AddColumn(#"Changed Type1", "No Dates", each if ([Live Supplier]=true and[CHAS Expiry Date]=true and [PI Insurance Expiry Date]=true and [Public Liability Expiry Date]=true and [Employers Liability Expiry]=true) then "no insurance data" else "ok"),
        #"Filtered Rows1" = Table.SelectRows(#"Added Custom5", each true)
    in
        #"Filtered Rows1"



    Thursday, January 3, 2019 1:14 PM

Answers

  • "Why do I have square brackets or is this a quirk of using the function buttons instead of writing the code in the Advanced Editor"

    The issue is that you are not familiar with Power Query syntax. Square brackets are used when you are referring to the current value in a column. So when you add a new column in a table, the syntax 'each […]' refers to each value in the column (who's name is in the brackets). 

    Why is the error checker saying no errors when there is no data in the created column

    You said previously that the column is full of errors. Which is correct - no data (null?) or is the word 'error' in the column?

    Why does the Advanced Editor have lots of Let clauses but only one In clause when all the examples I see online have there own let and in clause?

    Every 'let' must have a corresponding 'in' clause. In the code you posted, I see only one 'let' and one 'in' clause. 

    This is the row that is not concatenating properly   #"Add Supplier Name and Ref C" = Table.AddColumn(#"Added Conditional Column", "Supplier Name and Ref", each [Supplier] &" "&Text.From[System Ref No])

    This is just user error. Text.From is missing the opening parenthesis - Text.From([System Ref No])



    Thursday, January 3, 2019 2:29 PM

All replies

  • "Why do I have square brackets or is this a quirk of using the function buttons instead of writing the code in the Advanced Editor"

    The issue is that you are not familiar with Power Query syntax. Square brackets are used when you are referring to the current value in a column. So when you add a new column in a table, the syntax 'each […]' refers to each value in the column (who's name is in the brackets). 

    Why is the error checker saying no errors when there is no data in the created column

    You said previously that the column is full of errors. Which is correct - no data (null?) or is the word 'error' in the column?

    Why does the Advanced Editor have lots of Let clauses but only one In clause when all the examples I see online have there own let and in clause?

    Every 'let' must have a corresponding 'in' clause. In the code you posted, I see only one 'let' and one 'in' clause. 

    This is the row that is not concatenating properly   #"Add Supplier Name and Ref C" = Table.AddColumn(#"Added Conditional Column", "Supplier Name and Ref", each [Supplier] &" "&Text.From[System Ref No])

    This is just user error. Text.From is missing the opening parenthesis - Text.From([System Ref No])



    Thursday, January 3, 2019 2:29 PM
  • Thank you so much for clarifying my queries, it has helped my understanding and thank you for pointing out my rookie error - should have looked at brackets, I now know to follow the same rules as if for excel when it comes to functions. 

    Once again I am much obliged

    Thursday, January 3, 2019 5:08 PM
  • You're very welcome. Glad to be of help.
    Thursday, January 3, 2019 5:12 PM