none
Please, Help Me Define a Simple Variable RRS feed

  • Question

  • Hi guys,

    I am running into a syntax issue I cannot figure out.

    Scenario (incorrect):

    ....

    Length=Text.Length(Text.Replace(Text.Replace(List.First(Text.Split(List.First(Text.Split(List.Last(Text.Split([Base URL],"keyword=")),"&")),"-")),"%20"," "),"%2B",""))-2,  

    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each Text.Replace(Text.Replace(Text.Start(List.First(Text.Split(List.First(Text.Split(List.Last(Text.Split([Base URL],"keyword=")),"&")),"-")),Length),"%20"," "),"%2B","")
    ....
    So first, I want to figure out the length of a substring in column [Base URL]. Then in the next step, I want to use this Length value as second value in the Text.Start function. 

    Any idea how to correctly define Length variable?

    PS: dont worry about the other embedded List/Text functions, those work fine, I just need an general advice about the definition of the Length variable.

    Thx

    DZ


    Sunday, October 2, 2016 7:28 PM

Answers

  • Hi Daniel,

    Is this what you're looking for?

    = Table.AddColumn(#"Renamed Columns", "Test", each
    let
        lengthOfString = Text.Length([Base URL])-2
    in
        Text.Start([Base URL], lengthOfString))

    You can use a "let" expression anywhere you like to define intermediate variables and break up long, unwieldy expressions.

    Ehren


    Tuesday, October 4, 2016 6:37 PM
    Owner

All replies

  • It should work as it is: The name of a step in the current query is a variable (just like the name of other queries in your current workbook - it's a bit like WYSIWYG).

    I'd just recommend to avoid names that could be also function names, so better use sth like LengthOfString for example.


    Imke Feldmann TheBIccountant.com

    Sunday, October 2, 2016 7:39 PM
    Moderator
  • ok, so I tried this simplified version:

      LengthOfString = Text.Length([Base URL])-2,
        #"Added Custom" = Table.AddColumn(LengthOfString, "Test", each Text.Start([Base URL],LengthOfString))

    and I am getting:

    Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

    Right after defining the varibale...

    Sunday, October 2, 2016 8:04 PM
  • the error message looks to be sort of misleading: 

    You are trying to add a column to sth that isn't a table. Although your use of LengthOfString as the number for the Text.Start seems to be correct, it isn't when it comes to providing the first argument for the "Table.AddColumn"-formula: In there needs to go a table and as you're using LengthOfString in there also, this wouldn't work. It need to be the name of the last step where the table is returned onto which you want to add this column.


    Imke Feldmann TheBIccountant.com

    Sunday, October 2, 2016 8:42 PM
    Moderator
  • ok, I missed that part, that is of course true but when I fix it to this (last 3 steps of the query):

    ----------    

    #"Renamed Columns" = Table.RenameColumns(#"XX - Campaign Type",{{"URL", "Base URL"}}),
        LengthOfString = Text.Length([Base URL])-2,
        #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Test", each Text.Start([Base URL],LengthOfString))
    in
        #"Added Custom"

    ----------

    I still keep getting the same message.

    DZ

    Monday, October 3, 2016 6:00 AM
  • Could it be that the step LengthOfStep doesn't return a number? You error-message would fit to that step rather than the following one as you seem to reference a whole column there instead of a record.


    Imke Feldmann TheBIccountant.com

    Monday, October 3, 2016 6:31 AM
    Moderator
  • Well, that's the problem I guess. I want to get the LengthOfString value for every row in the Base URL column and then use it as variable in the next step for respective rows. 

    So say, LengthOfString for row 1 is 10, for row 2 it is 20. Then I want to use 10 in the next step for the row 1, 20 for the row 2 etc... 


    Monday, October 3, 2016 10:10 AM
  • Yes, that's what I was guessing.

    So you add a column that calculates the length for each row and then you need another column that uses it as a parameter/variable. Pls pay attention to the new syntax in the last step: LengthOfText is now in square brackets in order to pick the current rows value from that column:

    #"Renamed Columns" = Table.RenameColumns(#"XX - Campaign Type",{{"URL", "Base URL"}}),
        LengthOfString = Table.AddColumn(#"Renamed Columns", "LengthOfString", Text.Length([Base URL])-2),
        #"Added Custom" = Table.AddColumn(#"LengthOfString", "Test", each Text.Start([Base URL],[LengthOfString]))
    in
        #"Added Custom"


    Imke Feldmann TheBIccountant.com


    Monday, October 3, 2016 10:33 AM
    Moderator
  • So basically, I still have to create a helper column which I wanted to omit at the beginning. 
    Monday, October 3, 2016 12:33 PM
  • For some reason this aspect has slipped my attention so far...

    If you're new to functional languages, you might find this article helpful: https://en.wikipedia.org/wiki/Functional_programming

    It doesn't seem to be unusual to use helper-columns to calculate intermediate results and then remove them before presenting the results. But of course you could nest it in as well (as you did in you other formulas):

    #"Renamed Columns" = Table.RenameColumns(#"XX - Campaign Type",{{"URL", "Base URL"}}),
    #"Added Custom" = Table.AddColumn(
    #"Renamed Columns", "Test", each Text.Start([Base URL],Text.Length([Base URL])-2))
    in
        #"Added Custom"


    Imke Feldmann TheBIccountant.com

    Monday, October 3, 2016 1:50 PM
    Moderator
  • Yeah, sure the problem is the nested formulas are so long that I will not be able to read them once I get back to the code e.g. one month later.

    Anyway, thx for your time. I was just wondering if there is something smarter than a helper column which I have been using for the past year but I guess the answer is no.

    DZ

    Monday, October 3, 2016 3:03 PM
  • Hi Daniel,

    Is this what you're looking for?

    = Table.AddColumn(#"Renamed Columns", "Test", each
    let
        lengthOfString = Text.Length([Base URL])-2
    in
        Text.Start([Base URL], lengthOfString))

    You can use a "let" expression anywhere you like to define intermediate variables and break up long, unwieldy expressions.

    Ehren


    Tuesday, October 4, 2016 6:37 PM
    Owner