none
PowerQuery Expression Error: cannot convert value of type List to type Number RRS feed

  • Question

  • Hi,

    Hope you can help met with the following. I created three files with tables generated with power query. I created a new file in which I made three new queries to import the three tables and a fourth one where I integrate these. So far, so good.

    In total I have about 500 columns now so in the PowerQuery editor they do not all show.  I get a message saying I have to select columns in because of size limits and a box to do so. Thing is that I want to change the order of all 500 columns. In order to do so I changed the order of two visible columns. This works fine. Power query now adds a new line of code. And then edited this line in the advanced editor. In the editor I added all the fields that where not visible in the normal editor. When I process this change I get the error message: Expression.Error: cannot convert a value of type List to type Number. Value=List Type=Type.

    Please advise.

    Kind regards,

    Bas


    • Edited by Bas v P Sunday, February 14, 2016 4:19 PM
    Sunday, February 14, 2016 4:13 PM

Answers

  • This error message doesn't seem to fit to the step you're describing. Sure that the error-message belongs to this last reordering step?

    Can you step through the single steps in the query editor after this last change has been made or don't the single steps show up any more there?


    Imke Feldmann TheBIccountant.com

    • Marked as answer by Bas v P Wednesday, February 17, 2016 10:02 AM
    Monday, February 15, 2016 5:23 PM
    Moderator

All replies

  • This error message doesn't seem to fit to the step you're describing. Sure that the error-message belongs to this last reordering step?

    Can you step through the single steps in the query editor after this last change has been made or don't the single steps show up any more there?


    Imke Feldmann TheBIccountant.com

    • Marked as answer by Bas v P Wednesday, February 17, 2016 10:02 AM
    Monday, February 15, 2016 5:23 PM
    Moderator
  • Hi Imke,

    No problems in the other steps of the query (see below). Problem starts with sorting. Table DL_Bronbata contains 288 columns (not 500 like I mentioned) . I don't see them all in the editor. If I add a sorting step by moving a column in the editor to another position it works as well. It adds a line of code that only contains the columns which are in the editor view. If a manually add the other columns this message appears. 

    let
        Bron = Table.NestedJoin(DL_Brondata_Opgeschoond,{"KlantID_WHNV"},#"DAfsrpaken_excl annuleringen",{"Klant ID"},"NewColumn",JoinKind.LeftOuter),
        #"NewColumn uitvouwen" = Table.ExpandTableColumn(Bron, "NewColumn", {"Datum Ingevoerd", "Planyo ID", "StartDatumTijd", "E-mail_input klant", "Telefoon_input klant", "Adviseur", "Notities admin", "Notities overig"}, {"Datum Ingevoerd", "Planyo ID", "StartDatumTijd", "E-mail_input klant", "Telefoon_input klant", "Adviseur", "Notities admin", "Notities overig"}),
        #"Aangepaste kolom toegevoegd1" = Table.AddColumn(#"NewColumn uitvouwen", "StartDatumTijd_tekst", each [StartDatumTijd]),
        #"Type gewijzigd" = Table.TransformColumnTypes(#"Aangepaste kolom toegevoegd1",{{"StartDatumTijd_tekst", type text}}),
        #"Waarde vervangen" = Table.ReplaceValue(#"Type gewijzigd",":00:00","00uur",Replacer.ReplaceText,{"StartDatumTijd_tekst"}),
        #"Aangepaste kolom toegevoegd" = Table.AddColumn(#"Waarde vervangen", "Naam bestand", each [KlantID_WHNV]&"_"&[Adviseur]&"_"&[StartDatumTijd_tekst]),
        #"Kolommen verwijderd2" = Table.RemoveColumns(#"Aangepaste kolom toegevoegd",{"StartDatumTijd_tekst", "Notities admin"})
    in
        #"Kolommen verwijderd2"


    Wednesday, February 17, 2016 9:50 AM
  • Hi Bas,

    the "sorting"-step of your columns you described should return a code like "Table.ReorderColumns...".

    But I cannot spot this step in the code you've provided.


    Imke Feldmann TheBIccountant.com

    Wednesday, February 17, 2016 9:57 AM
    Moderator
  • Hi Imke,

    Found the problem...and I am really ashamed: use a column label twice in my formula.

    Thanks for taking the time to respond.

    Wednesday, February 17, 2016 10:02 AM