I have been struggling with a problem in excel power query/DAX and have finally given up. Hope someone here has a suggestion how to proceed. If it is solvable at all.

I have a table with registration numbers in one column and a date in the other (parking lot camera that registers license plate on each car that enters the lot).

I can count the frequency of each reg. nr. but my problem is, that I would like to end up with a table that shows how many plates have frequency =1, 2,3 etc. in a given period selected by the user. In the perfect world I could use the outcome of the frequency calculation as rows in a pivot table and then count how many times each value appears. But obviously that is not possible.

I have no problem adding a table to the data model with a single column containing numbers from min(frequency) - max(frequency) and then use this as rows.

For the example, the desired output should look like

Hope the above makes sense.

Thanks in advance.

Best regards Anders

I have been struggling with a problem in excel power query/DAX and have finally given up. Hope someone here has a suggestion how to proceed. If it is solvable at all.

I have a table with registration numbers in one column and a date in the other (parking lot camera that registers license plate on each car that enters the lot).

I can count the frequency of each reg. nr. but my problem is, that I would like to end up with a table that shows how many plates have frequency =1, 2,3 etc. in a given period selected by the user. In the perfect world I could use the outcome of the frequency calculation as rows in a pivot table and then count how many times each value appears. But obviously that is not possible.

I have no problem adding a table to the data model with a single column containing numbers from min(frequency) - max(frequency) and then use this as rows.

For the example, the desired output should look like

Hope the above makes sense.

Thanks in advance.

Best regards Anders

• excel
• dax
• frequency

Share Improve this question Follow asked Jan 31 at 9:25 Anders AndersenAnders Andersen 4511 silver badge44 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

let

Source = Excel.CurrentWorkbook(){[Name="TableReg"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Reg", type text}, {"Entrance", type date}}),


SourceEnd = Excel.CurrentWorkbook(){[Name="TableEnd"]}[Content],
#"Changed TypeEnd" = Table.TransformColumnTypes(SourceEnd,{{"Period End", type date}}),
#"Period End" = List.Min(#"Changed TypeEnd"[Period End]),

SourceStart = Excel.CurrentWorkbook(){[Name="TableStart"]}[Content],
#"Changed TypeStart" = Table.TransformColumnTypes(SourceStart,{{"Period Start", type date}}),
#"Period Start" = List.Max(#"Changed TypeStart"[Period Start]),


#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Entrance] >= #"Period Start"  and [Entrance] <= #"Period End" ),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Reg"}, {{"Anzahl", each Table.RowCount(_), type number}}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Reg"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Anzahl", "No of visits"}}),

#"Grouped Rows1" = Table.Group(#"Renamed Columns", {"No of visits"}, {{"Count of reg", each Table.RowCount(_), type number}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows1",{{"No of visits", Order.Descending}})
in

#"Sorted Rows"

The above code works in my sample file with Power Query. The large blue table is named TableReg, the table with the start date is named TableStart and the table with the end date is named TableEnd.