none
Problems with a character chain when importing from a Query RRS feed

  • Question

  • Hello
    I am not an expert in M language, so, I am having problems with the manipulation of a chain of characters that comes from an excel workbook.
    If i define a constant like this, the code gives no bug:
    Año = "2014",
    FicheroGespas = "C:\Users\SE50650\Documents\PQ\Gespas_2014.xlsx"

    But if the chain comes from the workbook, when i try to remove certain columns from the table, the error displayed is:
    (step 'FirstRowAsHeader') references queries or steps so may not directly access a data source. Please rebuild this data combination'.
    Can you help me with this?

    This is the code:
    let  
            EsteExcel = Excel.CurrentWorkbook(),
            Extraer_Parámetros = EsteExcel{[Name="QueryParametros"]}[Content],
            FicheroGespas = List.First( Table.Column(Extraer_Parámetros, "FicheroFuente")),
           Año = Text.Start(Text.End(FicheroGespas, 9), 4),
     
        //-----this works fine------------------------------------
        //Año = "2014",
        //FicheroGespas = "C:\Users\SE50650\Documents\PQ\Gespas_2014.xlsx",
        //-------------------------------------------------------
       
        TodosLosMeses = {"ENE", "FEB", "MAR", "ABR", "MAY", "JUN", "JUL", "AGO", "SEP", "OCT", "NOV","DIC"},
       
        FiltroUnidades = "%",
        MesActual = Date.Month(DateTime.LocalNow()),
        MesesConservar = if (Date.Year(DateTime.LocalNow()) <= Value.FromText(Año)) then List.Range(TodosLosMeses, 0, MesActual-1) else TodosLosMeses,
        CamposConservar = {"Código SAP", "Ud"} & MesesConservar,


        Source = Excel.Workbook(File.Contents(FicheroGespas)),
        #"Resumen _Sheet" = Source{[Item="Resumen ",Kind="Sheet"]}[Data],
        FirstRowAsHeader = Table.PromoteHeaders(#"Resumen _Sheet"),
        ListaInicialColumnas = Table.ColumnNames(FirstRowAsHeader),
        CamposNOdeseados = List.RemoveItems(ListaInicialColumnas, CamposConservar),
        RemovedColumns = Table.RemoveColumns(FirstRowAsHeader, CamposNOdeseados)
        /*error=(step 'FirstRowAsHeader') references queries or steps so may not directly access a data source. Please rebuild this data combiantion'

    in  RemovedColumns

    I am using the last version of PQ with Excel 2010. Many Thanks

    Tuesday, June 17, 2014 1:08 PM

Answers

  • This error means that the Power Query firewall feature is stepping in and trying to make sure you aren't leaking any information unintentionally. In this case, if you're not accessing any external and/or untrusted data sources, then you can click the "Fast Combine" button in the Power Query Excel ribbon and you shouldn't get this error anymore.

    Here is more information about the Fast Combine button:
    http://office.microsoft.com/en-us/excel-help/privacy-levels-HA104009800.aspx

    • Marked as answer by JLRB Wednesday, June 18, 2014 7:55 AM
    Tuesday, June 17, 2014 6:01 PM
    Moderator

All replies

  • This error means that the Power Query firewall feature is stepping in and trying to make sure you aren't leaking any information unintentionally. In this case, if you're not accessing any external and/or untrusted data sources, then you can click the "Fast Combine" button in the Power Query Excel ribbon and you shouldn't get this error anymore.

    Here is more information about the Fast Combine button:
    http://office.microsoft.com/en-us/excel-help/privacy-levels-HA104009800.aspx

    • Marked as answer by JLRB Wednesday, June 18, 2014 7:55 AM
    Tuesday, June 17, 2014 6:01 PM
    Moderator
  • It works perfectly!. Many thanks indeed

    Kind Regards

    Wednesday, June 18, 2014 7:47 AM