none
dcount function in power query RRS feed

  • Question

  • Hi,

    I have a query generated in power query that gives me the following results:

    ID          VALUES             

    1              A001

    2              A002

    3              A001

    4              B001

    5              B002

    6              B001

    I need to insert a new calculated field in the query I of the order of repetition of each record. The result we want to achieve is the following:

    ID          VALUES       RESULT 

    1              A001             1

    2              A002             1

    3              A001             2

    4              B001             1

    5              B002             1

    6              B001             2

    Using access get it using the following calculated field in a query: " DCont("*";"tabla1";"Valor='" & [valor] & "'and Id_prueba<=" & [id_prueba])". How I can do the same with power query?

    Thank you very much in advance and Merry Christmas to all,

    José Luis



    • Edited by joseluis88 Friday, December 26, 2014 1:45 PM
    Friday, December 26, 2014 12:48 PM

Answers

  • Hi,

    As per your code, the final table in your code have 5 columns (ID, schedule.booking-date.Element:Text, schedule.activity-id.Element:Text, schedule.employee-id.Element:Text, VALUES ). If you want to keep all these columns and add one more "RESULT" column. You can use below code in Advanced Editor windows.

    let
        Origen = Xml.Tables(Web.Contents("http://localhost:xxxx/planning_units/1001/schedule/2014-09-29.xml?end_date=2015-12-31&level=schedule&auth=xxxxxxxxxxxxxxxx=")),

        #"Tipo cambiado" = Table.TransformColumnTypes(Origen,{{"Attribute:type", type text}}),

        #"Expandir schedule" = Table.ExpandTableColumn(#"Tipo cambiado", "schedule", {"activity-id", "booking-date", "employee-id"}, {"schedule.activity-id", "schedule.booking-date", "schedule.employee-id"}),

        #"Expandir schedule.activity-id" = Table.ExpandTableColumn(#"Expandir schedule", "schedule.activity-id", {"Element:Text"}, {"schedule.activity-id.Element:Text"}),

        #"Expandir schedule.booking-date" = Table.ExpandTableColumn(#"Expandir schedule.activity-id", "schedule.booking-date", {"Element:Text"}, {"schedule.booking-date.Element:Text"}),

        #"Expandir schedule.employee-id" = Table.ExpandTableColumn(#"Expandir schedule.booking-date", "schedule.employee-id", {"Element:Text"}, {"schedule.employee-id.Element:Text"}),

        #"Índice agregado" = Table.AddIndexColumn(#"Expandir schedule.employee-id", "Índice", 1, 1),

        #"Columnas reordenadas" = Table.ReorderColumns(#"Índice agregado",{"Índice", "schedule.activity-id.Element:Text", "schedule.booking-date.Element:Text", "schedule.employee-id.Element:Text", "Attribute:type"}),

        #"Columnas con nombre cambiado" = Table.RenameColumns(#"Columnas reordenadas",{{"Índice", "ID"}}),

        #"Columnas quitadas" = Table.RemoveColumns(#"Columnas con nombre cambiado",{"Attribute:type"}),

        #"Columnas reordenadas1" = Table.ReorderColumns(#"Columnas quitadas",{"ID", "schedule.booking-date.Element:Text", "schedule.activity-id.Element:Text", "schedule.employee-id.Element:Text"}),

        #"Personalizada agregada" = Table.AddColumn(#"Columnas reordenadas1", "VALUE", each [#"schedule.booking-date.Element:Text"]&[#"schedule.activity-id.Element:Text"]&[#"schedule.employee-id.Element:Text"]),

      Group = Table.Group( #"Personalizada agregada" , "VALUE", {"Group", each Table.AddIndexColumn( Table.Sort(_, {"ID", 0 } ) , "RESULT" ,1,1 ), type table} ),

        #"Expand Group" = Table.ExpandTableColumn(Group, "Group", {"ID", "RESULT", "schedule.activity-id.Element:Text", "schedule.booking-date.Element:Tex", "schedule.employee-id.Element:Text"}, {"ID", "RESULT", "schedule.activity-id.Element:Text", "schedule.booking-date.Element:Tex", "schedule.employee-id.Element:Text"}),

        #"Reordered Columns" = Table.ReorderColumns(#"Expand Group",{"ID", "schedule.activity-id.Element:Text", "schedule.booking-date.Element:Tex", "schedule.employee-id.Element:Text", "VALUE", "RESULT"}),

        #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"ID", Order.Ascending}})
    in
        #"Sorted 

    Regards,

     
    • Marked as answer by joseluis88 Monday, December 29, 2014 12:56 PM
    Monday, December 29, 2014 11:46 AM

All replies

  • Hi,

    You can can use Table.Group and  Table.AddIndexColumn function to solve this.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Group = Table.Group(Source, {"VALUES"}, {{"Group", each Table.AddIndexColumn( Table.Sort(_, {"ID", 0 } ) , "RESULT" ,1,1 ), type table}}),
        Final = Table.Sort( Table.Combine( Group[Group] ) , { "ID", 0 } )
    in
        Final

    Regards,


    Friday, December 26, 2014 2:57 PM
  • Hi,

    First thank you very much for your answer. Could you please tell me where I have to enter the code you indicate in your response ?, the query´s code where I need to create the RESULT field is as follows:

    let

        Origen = Xml.Tables(Web.Contents("http://localhost:xxxx/planning_units/1001/schedule/2014-09-29.xml?end_date=2015-12-31&level=schedule&auth=xxxxxxxxxxxxxxxx=")),

        #"Tipo cambiado" = Table.TransformColumnTypes(Origen,{{"Attribute:type", type text}}),

        #"Expandir schedule" = Table.ExpandTableColumn(#"Tipo cambiado", "schedule", {"activity-id", "booking-date", "employee-id"}, {"schedule.activity-id", "schedule.booking-date", "schedule.employee-id"}),

        #"Expandir schedule.activity-id" = Table.ExpandTableColumn(#"Expandir schedule", "schedule.activity-id", {"Element:Text"}, {"schedule.activity-id.Element:Text"}),

        #"Expandir schedule.booking-date" = Table.ExpandTableColumn(#"Expandir schedule.activity-id", "schedule.booking-date", {"Element:Text"}, {"schedule.booking-date.Element:Text"}),

        #"Expandir schedule.employee-id" = Table.ExpandTableColumn(#"Expandir schedule.booking-date", "schedule.employee-id", {"Element:Text"}, {"schedule.employee-id.Element:Text"}),

        #"Índice agregado" = Table.AddIndexColumn(#"Expandir schedule.employee-id", "Índice", 1, 1),

        #"Columnas reordenadas" = Table.ReorderColumns(#"Índice agregado",{"Índice", "schedule.activity-id.Element:Text", "schedule.booking-date.Element:Text", "schedule.employee-id.Element:Text", "Attribute:type"}),

        #"Columnas con nombre cambiado" = Table.RenameColumns(#"Columnas reordenadas",{{"Índice", "ID"}}),

        #"Columnas quitadas" = Table.RemoveColumns(#"Columnas con nombre cambiado",{"Attribute:type"}),

        #"Columnas reordenadas1" = Table.ReorderColumns(#"Columnas quitadas",{"ID", "schedule.booking-date.Element:Text", "schedule.activity-id.Element:Text", "schedule.employee-id.Element:Text"}),

        #"Personalizada agregada" = Table.AddColumn(#"Columnas reordenadas1", "VALUE", each [#"schedule.booking-date.Element:Text"]&[#"schedule.activity-id.Element:Text"]&[#"schedule.employee-id.Element:Text"])

    in

        #"Personalizada agregada

    Thank you very much in advance,

    José Luis


    • Edited by joseluis88 Monday, December 29, 2014 8:44 AM
    Monday, December 29, 2014 8:43 AM
  • Hi,

    You can can use Table.Group and  Table.AddIndexColumn function to solve this.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Group = Table.Group(Source, {"VALUES"}, {{"Group", each Table.AddIndexColumn( Table.Sort(_, {"ID", 0 } ) , "RESULT" ,1,1 ), type table}}),
        Final = Table.Sort( Table.Combine( Group[Group] ) , { "ID", 0 } )
    in
        Final

    Regards,

    Hi,

    First thank you very much for your answer. Could you please tell me where I have to enter the code you indicate in your response ?, the query´s code where I need to create the RESULT field is as follows:

    let

        Origen = Xml.Tables(Web.Contents("http://localhost:xxxx/planning_units/1001/schedule/2014-09-29.xml?end_date=2015-12-31&level=schedule&auth=xxxxxxxxxxxxxxxx=")),

        #"Tipo cambiado" = Table.TransformColumnTypes(Origen,{{"Attribute:type", type text}}),

        #"Expandir schedule" = Table.ExpandTableColumn(#"Tipo cambiado", "schedule", {"activity-id", "booking-date", "employee-id"}, {"schedule.activity-id", "schedule.booking-date", "schedule.employee-id"}),

        #"Expandir schedule.activity-id" = Table.ExpandTableColumn(#"Expandir schedule", "schedule.activity-id", {"Element:Text"}, {"schedule.activity-id.Element:Text"}),

        #"Expandir schedule.booking-date" = Table.ExpandTableColumn(#"Expandir schedule.activity-id", "schedule.booking-date", {"Element:Text"}, {"schedule.booking-date.Element:Text"}),

        #"Expandir schedule.employee-id" = Table.ExpandTableColumn(#"Expandir schedule.booking-date", "schedule.employee-id", {"Element:Text"}, {"schedule.employee-id.Element:Text"}),

        #"Índice agregado" = Table.AddIndexColumn(#"Expandir schedule.employee-id", "Índice", 1, 1),

        #"Columnas reordenadas" = Table.ReorderColumns(#"Índice agregado",{"Índice", "schedule.activity-id.Element:Text", "schedule.booking-date.Element:Text", "schedule.employee-id.Element:Text", "Attribute:type"}),

        #"Columnas con nombre cambiado" = Table.RenameColumns(#"Columnas reordenadas",{{"Índice", "ID"}}),

        #"Columnas quitadas" = Table.RemoveColumns(#"Columnas con nombre cambiado",{"Attribute:type"}),

        #"Columnas reordenadas1" = Table.ReorderColumns(#"Columnas quitadas",{"ID", "schedule.booking-date.Element:Text", "schedule.activity-id.Element:Text", "schedule.employee-id.Element:Text"}),

        #"Personalizada agregada" = Table.AddColumn(#"Columnas reordenadas1", "VALUE", each [#"schedule.booking-date.Element:Text"]&[#"schedule.activity-id.Element:Text"]&[#"schedule.employee-id.Element:Text"])

    in

        #"Personalizada agregada

    Thank you very much in advance,

    José Luis



    Monday, December 29, 2014 10:09 AM
  • Hi,

    As per your code, the final table in your code have 5 columns (ID, schedule.booking-date.Element:Text, schedule.activity-id.Element:Text, schedule.employee-id.Element:Text, VALUES ). If you want to keep all these columns and add one more "RESULT" column. You can use below code in Advanced Editor windows.

    let
        Origen = Xml.Tables(Web.Contents("http://localhost:xxxx/planning_units/1001/schedule/2014-09-29.xml?end_date=2015-12-31&level=schedule&auth=xxxxxxxxxxxxxxxx=")),

        #"Tipo cambiado" = Table.TransformColumnTypes(Origen,{{"Attribute:type", type text}}),

        #"Expandir schedule" = Table.ExpandTableColumn(#"Tipo cambiado", "schedule", {"activity-id", "booking-date", "employee-id"}, {"schedule.activity-id", "schedule.booking-date", "schedule.employee-id"}),

        #"Expandir schedule.activity-id" = Table.ExpandTableColumn(#"Expandir schedule", "schedule.activity-id", {"Element:Text"}, {"schedule.activity-id.Element:Text"}),

        #"Expandir schedule.booking-date" = Table.ExpandTableColumn(#"Expandir schedule.activity-id", "schedule.booking-date", {"Element:Text"}, {"schedule.booking-date.Element:Text"}),

        #"Expandir schedule.employee-id" = Table.ExpandTableColumn(#"Expandir schedule.booking-date", "schedule.employee-id", {"Element:Text"}, {"schedule.employee-id.Element:Text"}),

        #"Índice agregado" = Table.AddIndexColumn(#"Expandir schedule.employee-id", "Índice", 1, 1),

        #"Columnas reordenadas" = Table.ReorderColumns(#"Índice agregado",{"Índice", "schedule.activity-id.Element:Text", "schedule.booking-date.Element:Text", "schedule.employee-id.Element:Text", "Attribute:type"}),

        #"Columnas con nombre cambiado" = Table.RenameColumns(#"Columnas reordenadas",{{"Índice", "ID"}}),

        #"Columnas quitadas" = Table.RemoveColumns(#"Columnas con nombre cambiado",{"Attribute:type"}),

        #"Columnas reordenadas1" = Table.ReorderColumns(#"Columnas quitadas",{"ID", "schedule.booking-date.Element:Text", "schedule.activity-id.Element:Text", "schedule.employee-id.Element:Text"}),

        #"Personalizada agregada" = Table.AddColumn(#"Columnas reordenadas1", "VALUE", each [#"schedule.booking-date.Element:Text"]&[#"schedule.activity-id.Element:Text"]&[#"schedule.employee-id.Element:Text"]),

      Group = Table.Group( #"Personalizada agregada" , "VALUE", {"Group", each Table.AddIndexColumn( Table.Sort(_, {"ID", 0 } ) , "RESULT" ,1,1 ), type table} ),

        #"Expand Group" = Table.ExpandTableColumn(Group, "Group", {"ID", "RESULT", "schedule.activity-id.Element:Text", "schedule.booking-date.Element:Tex", "schedule.employee-id.Element:Text"}, {"ID", "RESULT", "schedule.activity-id.Element:Text", "schedule.booking-date.Element:Tex", "schedule.employee-id.Element:Text"}),

        #"Reordered Columns" = Table.ReorderColumns(#"Expand Group",{"ID", "schedule.activity-id.Element:Text", "schedule.booking-date.Element:Tex", "schedule.employee-id.Element:Text", "VALUE", "RESULT"}),

        #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"ID", Order.Ascending}})
    in
        #"Sorted 

    Regards,

     
    • Marked as answer by joseluis88 Monday, December 29, 2014 12:56 PM
    Monday, December 29, 2014 11:46 AM
  • Hi,

    Thank you so much, in principle works perfectly. Now I have to check that the results are correct crossing data we obtain using a formula in excel (countif).
    Thanks again and Happy New Year,

    José Luis


    • Edited by joseluis88 Monday, December 29, 2014 12:56 PM
    Monday, December 29, 2014 12:56 PM
  • You are welcome. Happy New Year...
    Monday, December 29, 2014 3:16 PM