none
Expression.Error: We cannot convert the value 0 to type Text. RRS feed

  • Question

  • Hi, 

    So early days for me coding in M code however I can't quite get my head around this one.  Essentially I am trying to duplicate the first row of a table in Power Query.  I need to do this as the particular format of the data has some really odd things in it that I want to remove from the Title but keep.  I.E.  Some of the column names would be:

    3 (building             )

    4 (ignored ) 5 (choice                        ) 6 (sword                          ) 7 (courage                                            ) 8 (sure) 9 (admitted) 10 (identifies ) 11 (pollute ) 12 (unusual ) 13 (sincerely                                           ) 14 (pleasing )

    So in my early days of working on this I figured that I would import the table then duplicate the first row.  Then I can remove everything between the brackets in 1 line and remove the number at the start in the 2nd line.  So far I have I am stumped with the duplication step.  When I run the below code I get the error

    Expression.Error: We cannot convert the value 0 to type Text. 

    let

        Source = Csv.Document(File.Contents("D:\utils\Sunlanda Data\SunLanda Data\2011\School0292_2011YR5.csv"),[Delimiter=",", Columns=168, Encoding=1252, QuoteStyle=QuoteStyle.None]),
        AddIndex = Table.AddIndexColumn(Source,"Index",0,1),
        Header = Table.SelectRows(AddIndex, each [Index] = 0),
        FinalTable = Table.InsertRows(AddIndex,1,Table.ToList(Header))
        in
        FinalTable

    If I add a line that changes the index into type text as follows:

    let
        Source = Csv.Document(File.Contents("D:\utils\Sunlanda Data\SunLanda Data\2011\School0292_2011YR5.csv"),[Delimiter=",", Columns=168, Encoding=1252, QuoteStyle=QuoteStyle.None]),
        AddIndex = Table.AddIndexColumn(Source,"Index",0,1),
        #"Changed Type" = Table.TransformColumnTypes(AddIndex,{{"Index", type text}}),
        Header = Table.SelectRows(#"Changed Type", each [Index] = "0"),
        FinalTable = Table.InsertRows(#"Changed Type",1,Table.ToList(Header))
    in
        FinalTable

    Then I get the error:

    We cannot convert the value "Batch,Last Name,Firs..." to type Record.

    The values listed are every single column name in the csv.

    Any thoughts or is there a better way of doing what I'm after.

    Wednesday, February 28, 2018 5:12 AM

Answers

  • The list that is required as the 2nd parameter in Table.InsertRows is actually a list for records (with one record per row, as you can insert multiple rows with this command). So you'd have to write it like this:

    Table.InsertRows(AddIndex,1,{Header{0}})

    It selects the first row from the Header table, thereby transforming it to a record (Header{0}) and by wrapping it into the curly brackets it puts that record into a list.

    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Marked as answer by leahy268 Wednesday, February 28, 2018 9:10 PM
    Wednesday, February 28, 2018 6:29 AM
    Moderator

All replies

  • The list that is required as the 2nd parameter in Table.InsertRows is actually a list for records (with one record per row, as you can insert multiple rows with this command). So you'd have to write it like this:

    Table.InsertRows(AddIndex,1,{Header{0}})

    It selects the first row from the Header table, thereby transforming it to a record (Header{0}) and by wrapping it into the curly brackets it puts that record into a list.

    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Marked as answer by leahy268 Wednesday, February 28, 2018 9:10 PM
    Wednesday, February 28, 2018 6:29 AM
    Moderator
  • So Say I had more than 1 row to insert then what would be the syntax.  This M code type conversions are kind of beating me a bit at the moment.  Although I have got a lot further since the first bit of help.

    Thanks

    Thursday, March 1, 2018 2:51 AM
  • It would be this:

    Table.InsertRows(AddIndex,1,{Header{0}, Header{1}})


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Thursday, March 1, 2018 6:22 AM
    Moderator