Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Importación datos datos servidor sql

Muy buenas.

 

Estoy realizando una conexión a una base de datos sql server, la cual tiene actualmente más de 2 millones de registros y crece diariamente. Necesito, a la hora de trabajar con ella en el editor de Power Query en Power Bi reducir el numero de registros para que sea mas ágil, pero que luego en el escritorio y cuando lo publique disponer del 100 % de los datos.

 

¿Me pueden indicar como se podría hacer?

 

Muchas gracias.

Tutorial Requests
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    Anonymous ,

     

    On that row, try 'if dataToggle' not 'If dataToggle' i.e. do not capitalise 'if'.

     

    Also, you have misspelled 'toggleStep' in the last line.

     

    Pete

8 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    4 years ago

    Hi Anonymous ,

    Create a new blank query called 'dataToggle' or similar. Just apply a single value of 1 to this query.

    In your main query with the 2 million rows, under the source/navigation steps, filter your data down to a smaller number of rows that you can work with while developing.

    In the M code, add a step that acts as a switch between the filtered version and the full version. It will look something like this:

     

    let
  Source = [Your SQL server details],
  Database = [Your database/table navigation],
  dataFilter = Table.SelectRows(Database, Your filter conditions),
  toggleStep = if dataToggle = 1 then dataFilter else Database,
  otherSteps = Continue your transformations here, using toggleStep as the previous step
in
  otherSteps

     

    This will give you a filtered dataset to develop with. When you want to publish, just change the '1' in the dataToggle query to another value ('0', or 'n' or anything that isn't '1') and it will unfilter your query ready for publishing.

     

    Pete

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Muy buenas, gracias por la respuesta.

       

      Así me quedó el código M, pero me da error "Exprression.SyntaxError: Se esperaba el token Comma".

       

      Es posible que la consulta datoToggle no la haya creado correctamente.

       

       

      let

          Origen = Sql.Databases("XXXXX.XX.XXXXXXXX\XXXXX,1111"),

      CCCCCC_CCCCCCCC = Origen{[Nombre=" CCCCCC_CCCCCCCC "]}[Datos],

          dbo_DDD_DDDDDDD = CCCCCC_CCCCCCCC {[Schema="dbo",Item=" DDD_DDDDDDD "]}[Data],

      #"Filas filtradas" = Table.SelectRows(dbo_ DDD_DDDDDDD, cada [FILA_ID] >= 652345 y [FILA_ID] <= 653345),

      toggleStep = If dataToggle = 1 then Filas Filtradas else dbo_DDD_DDDDDD,

      otherSteps = Continuar las transformaciones liebre, usando toggleStep como paso anterior,

      in

      otherSteps

      • BA_Pete's avatar
        BA_Pete
        Super User
        4 years ago

        Hi Anonymous ,

         

        You need to correctly reference the different steps in your query, something like this:

         

        let
    Origen = Sql.Databases("XXXXX.XX.XXXXXXXX\XXXXX,1111"),
    CCCCCC_CCCCCCCC = Origen{[Name=" CCCCCC_CCCCCCCC "]}[Data],
    dbo_DDD_DDDDDDD = CCCCCC_CCCCCCCC {[Schema="dbo",Item=" DDD_DDDDDDD "]}[Data],
    #"Filas filtradas" = Table.SelectRows(dbo_ DDD_DDDDDDD, each [FILA_ID] >= 652345 and [FILA_ID] <= 653345),
    toggleStep = if dataToggle = 1 then #"Filas filtradas" else dbo_DDD_DDDDDDD,
    nextStep = Any.Function(toggleStep, function arguments),
    anotherStep = Another.Function(nextStep, function arguments),
    ...
    ...
    lastStep = Last.Function(previousStepName, function arguments)
in
    lastStep

         

         

        You can see more clearly here how toggleStep references the steps before/after filtering and switches between them to change the table that is carried forward into the following steps.

        Ignore everything after toggleStep as these are just example rows showing how each row references the previous one correctly. Power Query will do this for you automatically.

         

        Pete