none
Countif function in power query RRS feed

  • Question

  • I have a XML file taken from the software Autodesk Naviswork. The file is being used for finding errors in our Architectural 3D models.

    When expanding the XML file i have several thounds rows of data. 

    Each test result is supposed to have 24 "rows" of data.
    However, some results only have 12 rows of data.

    I need to filter these out of the report.
    My intention was to add a Countif column in power query, count the testname, and simply remove the test results that does not have 24 rows as a result.

    However i am unable to find a functional way of adding a countif column.
    I need data from all 24 rows, so using group function doesent seem to be a option.

    Monday, April 30, 2018 11:35 AM

Answers

  • not sure if it works with XML, but I assume the expand step operates on a table.
    BEFORE expanding add a new step as below:

    = Table.AddColumn(BeforeExpand, "NrOfRows", each Table.RowCount([Column]))

    or if you use the UI to add new column just paste the following:

    Table.RowCount([Column])
    where Column is the name of the table from XML

    Monday, April 30, 2018 12:27 PM

All replies

  • not sure if it works with XML, but I assume the expand step operates on a table.
    BEFORE expanding add a new step as below:

    = Table.AddColumn(BeforeExpand, "NrOfRows", each Table.RowCount([Column]))

    or if you use the UI to add new column just paste the following:

    Table.RowCount([Column])
    where Column is the name of the table from XML

    Monday, April 30, 2018 12:27 PM
  • Never been this close to fixing it. But sadly it didnt work.

    It seems that each record contains 12 rows, not 24.

    When using your method i see 24 rows with the same test name, and 12 as a result in all rows.
    Any suggestions as to how i should proceed?

    Monday, April 30, 2018 1:48 PM
  • could it be that sometimes the record has only one field, and sometimes it has two?
    there is a function that takes record as an argument, maybe you can try it?
    Record.FieldCount([RecordColumn])
    EDIT record with two fields does not make much sense - is it possible that single test can have data coming from multiple source files?


    Monday, April 30, 2018 2:03 PM
  • You were right! -> SOLVED.
    I actualy used the formula that you posted previously. But applied it a few steps back.
    Apparently i had a table expansions before. That expansion was supposed to have 2 "rows" x 12 Results.
    But some were 1 row x 12 results.

    When reviewing the rows that had 1 as count value, i quickly began celebrating. Those were that once that should be filtered. And so i did.

    Big help - Thanks alot. :D
    Monday, April 30, 2018 2:28 PM
  • happy to help :)
    Monday, April 30, 2018 3:19 PM