Keeping value count but removing duplicates

[abeirne]

Hi all, I am calculating occupancy rates and in my table I have multiple dates, the site occupied, and a calculated column that counts the number of site types reserved for that day. So January 1 included 27 Standard RV Sites, 2 Premium Lakeside, and 1 cottage. I would like the sum of the day so I remove Date&Site Type duplicates, but then my calculated column numbers change and are incorrect. Is there a way to remove duplicates without changing the count or perhaps something different in power query?


Maybe a single date table attached to the dates? But I also need to have the categories connected to my categories table. Thank you so much for the help.

[Anonymous · Accepted solution]

Hi @abeirne ,

I have created a simple sample, please refer to my pbix file to see if it helps you.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlbSUTIyMDLUt9A3NAKyDY2MDU3NgIyICKVYnWglEyR5kHBKWkYKkIqMBMuaIsmaA9lmpqmlqUA6KgosDTYRJm8JZAf4K7gHKxgYmICMciRCjRMRapyJUONChBpXItS4EaHGXSk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Date = _t, Reference = _t, Notes = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Date", type date}, {"Reference", type text}, {"Notes", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Id"}, {{"Data", each Table.AddIndexColumn(_,"Index",1,1), type table [Id=nullable number, Date=nullable date, Reference=nullable text, Notes=nullable text,Index = nullable number]}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Date", "Reference", "Notes", "Index"}, {"Date", "Reference", "Notes", "Index"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Data",each [Id],each if [Index] <> 1 then null else [Id],Replacer.ReplaceValue,{"Id"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each [Date],each if [Index] <> 1 then null else [Date],Replacer.ReplaceValue,{"Date"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",each [Reference],each if [Index] <> 1 then null else [Reference],Replacer.ReplaceValue,{"Reference"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value2",{"Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Id", Int64.Type}, {"Date", type date}, {"Reference", type text}, {"Notes", type text}})
in
    #"Changed Type1"

If I have misunderstood your meaning, please provide your pbix file wtihout privacy information and desired output with more details( Or some sample data).

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Barthel]

Hey,

I think the most useful is to apply a group by to the data in Power Query. Right click on the table and select 'Group By'.

Choose advanced. Select the 'Date' and 'Unit/Site Type' column at the top. And add a new column at the bottom that takes the sum of 'OccupiedQ'.