Reply
Frequent Visitor
Posts: 4
Registered: ‎03-20-2017
Accepted Solution

Expression Error in the Tutorial

Hello Everyone,

 

im quite new to Power BI thats why I tried learning it with following this Tutorial which is the German Version of this Tutorial.

 

I followed all the steps, which i found easy, until it came to point where i want so save the query changes. I tried it several times to prevent any missclicks but every time i get the same error message (unfortunately in german)

 

It says: Expression.Error A value type "list" cannot be converted to the type "text"

 

I have no idea what i am doing wrong. I just followed the Tutorial step by step. Is there advice for me? Or can anyone tell me the original english error message so i can google that? I had no succes with googling my translation or the german one.

 

Thank you very much

 

Manu


Accepted Solutions
Highlighted
New Contributor
Posts: 538
Registered: ‎11-25-2016

Re: Expression Error in the Tutorial

A little late, due to other business, but still.

 

First of all: I think you did nothing wrong.

 

I noticed that the contents from the web page keep changing: in the step "Quelle" you can see in ClassName that the table is sortable. may be that explains it, but I don't know any specifics about that.

In the tutorial it is stated that the top 2 rows should be removed.

Well, sometimes those rows are just not there and there is nothing to remove. I guess this was the case when you created your queries. However, if you refresh a few times, you may notice that it keeps changing, so it can be that everything runs fine in the Query Editor, but when loading the queries, the web page is refreshed with different data (sort) and the query crashes.

 

The solution would fit in some advanced tutoral: dynamically remove top rows.

In the 3rd line of the code below you see Table.Skip with a condition as the second argument.

According to the instructions in the tutorial that would have been just a 2: Table.Skip(Data0, 2).

The second argument of Table.kip is called countOrCondition, so you can provide 2 types of arguments.

In case of a condition it means that all rows are removed until the first row that doesn't meet the condition, so in this case all rows are removed until the value "United States of America" in column [Name and status of region].

 

Also the second last row (where columns are removed) didn't work with your code, so I replaced it.

It's also best practice to select the columns you want to keep and then choose "Remove Other Columns" (instead of selecting columns you want to remove and remove those).

This will still work if something changes in the columns you don't want.

 

The other query was fine, no changes.

 

Hopefully this will get you back on track with your Power Query study!

 

let
    Quelle = Web.Page(Web.Contents("http://en.wikipedia.org/wiki/List_of_U.S._state_abbreviations")),
    Data0 = Quelle{0}[Data],
    #"Removed Top Rows" = Table.Skip(Data0, each [Name and status of region] <> "United States of America"),
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Removed Top Rows",{{"Header", type text}, {"Name and status of region", type text}, {"Name and status of region2", type text}, {"ISO", type text}, {"ANSI", type text}, {"ANSI2", Int64.Type}, {"USPS", type text}, {"USCG", type text}, {"GPO", type text}, {"AP", type text}, {"Other #(lf)abbreviations", type text}}),
    #"Entfernte untere Zeilen" = Table.RemoveLastN(#"Geänderter Typ",26),
    #"Gefilterte Zeilen" = Table.SelectRows(#"Entfernte untere Zeilen", each ([Name and status of region2] <> "Federal district")),
    #"Höher gestufte Header" = Table.PromoteHeaders(#"Gefilterte Zeilen", [PromoteAllScalars=true]),
    #"Removed Other Columns" = Table.SelectColumns(#"Höher gestufte Header",{"United States of America", "US"}),
    #"Umbenannte Spalten" = Table.RenameColumns(#"Removed Other Columns",{{"United States of America", "State Name"}, {"US", "State Code"}})
in
    #"Umbenannte Spalten"
Specializing in Power Query Formula Language (M)

View solution in original post


All Replies
New Contributor
Posts: 538
Registered: ‎11-25-2016

Re: Expression Error in the Tutorial

Hi,

 

If you go to the Advanced Editor, copy your code and paste it over here, then we will be better able to help you.

(Before you paste the code, press the code icon).

If it's in German, that's not too bad. Some people here speak German fluently.

 

 

Specializing in Power Query Formula Language (M)
Frequent Visitor
Posts: 4
Registered: ‎03-20-2017

Re: Expression Error in the Tutorial

Thanks for the advice, here it is:

 

let
    Quelle = Web.Page(Web.Contents("http://www.bankrate.com/finance/retirement/best-places-retire-how-state-ranks.aspx")),
    Data0 = Quelle{0}[Data],
    #"Ersetzter Wert" = Table.ReplaceValue(Data0,"27 (tied)","27",Replacer.ReplaceText,{"Health care quality"}),
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Ersetzter Wert",{{"Header", type text}, {"Overall rank", Int64.Type}, {"State", type text}, {"Cost of living", Int64.Type}, {"Crime rate", Int64.Type}, {"Community well-being", Int64.Type}, {"Health care quality", Int64.Type}, {"Tax rate", Int64.Type}, {"Weather", Int64.Type}}),
    #"Entfernte Spalten" = Table.RemoveColumns(#"Geänderter Typ",{"Header"}),
    #"Zusammenführte Abfragen" = Table.NestedJoin(#"Entfernte Spalten",{"State"},#"State Codes",{"State Name"},"NewColumn",JoinKind.LeftOuter),
    #"Erweiterte NewColumn" = Table.ExpandTableColumn(#"Zusammenführte Abfragen", "NewColumn", {"State Code"}, {"State Code"})
in
    #"Erweiterte NewColumn"

New Contributor
Posts: 538
Registered: ‎11-25-2016

Re: Expression Error in the Tutorial

Please also supply the "State Codes" query.

Specializing in Power Query Formula Language (M)
Frequent Visitor
Posts: 4
Registered: ‎03-20-2017

Re: Expression Error in the Tutorial

Oh sorry! Here you go:

 

let
    Quelle = Web.Page(Web.Contents("http://en.wikipedia.org/wiki/List_of_U.S._state_abbreviations")),
    Data0 = Quelle{0}[Data],
    #"Geänderter Typ" = Table.TransformColumnTypes(Data0,{{"Header", type text}, {"Name and status of region", type text}, {"Name and status of region2", type text}, {"ISO", type text}, {"ANSI", type text}, {"ANSI2", Int64.Type}, {"USPS", type text}, {"USCG", type text}, {"GPO", type text}, {"AP", type text}, {"Other #(lf)abbreviations", type text}}),
    #"Entfernte untere Zeilen" = Table.RemoveLastN(#"Geänderter Typ",26),
    #"Gefilterte Zeilen" = Table.SelectRows(#"Entfernte untere Zeilen", each ([Name and status of region2] <> "Federal district")),
    #"Höher gestufte Header" = Table.PromoteHeaders(#"Gefilterte Zeilen", [PromoteAllScalars=true]),
    #"Entfernte Spalten" = Table.RemoveColumns(#"Höher gestufte Header",{"Codes:#(lf)#(lf)#(lf)    ISO#(lf)ISO 3166 codes (2-letter, 3-letter and 3-digit codes from ISO 3166-1; 2+2-letter codes from ISO 3166-2)#(lf)#(lf)#(lf)    ANSI#(lf)2-letter and 2-digit codes from the ANSI standard INCITS 38:2009#(lf)#(lf)#(lf)    USPS#(lf)2-letter codes used by the United States Postal Service#(lf)#(lf)#(lf)    USCG#(lf)2-letter codes used by the United States Coast Guard (red text shows differences between ANSI and USPS)#(lf)#(lf)#(lf)Abbreviations:#(lf)#(lf)#(lf)    GPO#(lf)Older variable-length official US Government Printing Office abbreviations#(lf)#(lf)#(lf)    AP#(lf)Abbreviations from the AP Stylebook (red text shows differences between GPO and AP)", "Federal state", "US#(cr)#(lf)#(lf)USA#(cr)#(lf)#(lf)840", "0", "Column7", "Column8", "U.S.", "U.S._1", "U.S.A."}),
    #"Umbenannte Spalten" = Table.RenameColumns(#"Entfernte Spalten",{{"United States of America", "State Name"}, {"US", "State Code"}})
in
    #"Umbenannte Spalten"

Highlighted
New Contributor
Posts: 538
Registered: ‎11-25-2016

Re: Expression Error in the Tutorial

A little late, due to other business, but still.

 

First of all: I think you did nothing wrong.

 

I noticed that the contents from the web page keep changing: in the step "Quelle" you can see in ClassName that the table is sortable. may be that explains it, but I don't know any specifics about that.

In the tutorial it is stated that the top 2 rows should be removed.

Well, sometimes those rows are just not there and there is nothing to remove. I guess this was the case when you created your queries. However, if you refresh a few times, you may notice that it keeps changing, so it can be that everything runs fine in the Query Editor, but when loading the queries, the web page is refreshed with different data (sort) and the query crashes.

 

The solution would fit in some advanced tutoral: dynamically remove top rows.

In the 3rd line of the code below you see Table.Skip with a condition as the second argument.

According to the instructions in the tutorial that would have been just a 2: Table.Skip(Data0, 2).

The second argument of Table.kip is called countOrCondition, so you can provide 2 types of arguments.

In case of a condition it means that all rows are removed until the first row that doesn't meet the condition, so in this case all rows are removed until the value "United States of America" in column [Name and status of region].

 

Also the second last row (where columns are removed) didn't work with your code, so I replaced it.

It's also best practice to select the columns you want to keep and then choose "Remove Other Columns" (instead of selecting columns you want to remove and remove those).

This will still work if something changes in the columns you don't want.

 

The other query was fine, no changes.

 

Hopefully this will get you back on track with your Power Query study!

 

let
    Quelle = Web.Page(Web.Contents("http://en.wikipedia.org/wiki/List_of_U.S._state_abbreviations")),
    Data0 = Quelle{0}[Data],
    #"Removed Top Rows" = Table.Skip(Data0, each [Name and status of region] <> "United States of America"),
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Removed Top Rows",{{"Header", type text}, {"Name and status of region", type text}, {"Name and status of region2", type text}, {"ISO", type text}, {"ANSI", type text}, {"ANSI2", Int64.Type}, {"USPS", type text}, {"USCG", type text}, {"GPO", type text}, {"AP", type text}, {"Other #(lf)abbreviations", type text}}),
    #"Entfernte untere Zeilen" = Table.RemoveLastN(#"Geänderter Typ",26),
    #"Gefilterte Zeilen" = Table.SelectRows(#"Entfernte untere Zeilen", each ([Name and status of region2] <> "Federal district")),
    #"Höher gestufte Header" = Table.PromoteHeaders(#"Gefilterte Zeilen", [PromoteAllScalars=true]),
    #"Removed Other Columns" = Table.SelectColumns(#"Höher gestufte Header",{"United States of America", "US"}),
    #"Umbenannte Spalten" = Table.RenameColumns(#"Removed Other Columns",{{"United States of America", "State Name"}, {"US", "State Code"}})
in
    #"Umbenannte Spalten"
Specializing in Power Query Formula Language (M)
Frequent Visitor
Posts: 4
Registered: ‎03-20-2017

Re: Expression Error in the Tutorial

Thank you very much for your help Marcel!

 

I thought that it had something to do with the data myself. What i dont get ist the Microsoft guys dont check their Tutorial regularily, especially for beginners thats not a nice onbaording experience. 

 

Anyway thank you for your help, i will go on trying because i think Power BI is a great tool Smiley Happy

Moderator
Posts: 1,530
Registered: ‎03-06-2016

Re: Expression Error in the Tutorial

@mmatusche

It is great that you confusion is clear. If no further questions, you can accept @MarcelBeug reply as solution to close this thread.