Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mmatusche
Regular Visitor

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

1 ACCEPTED SOLUTION

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

7 REPLIES 7
mmatusche
Regular Visitor

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"

Please also supply the "State Codes" query.

Specializing in Power Query Formula Language (M)

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"

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)

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 🙂

@mmatusche

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

MarcelBeug
Community Champion
Community Champion

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)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.