Forum Discussion
Distinct Count with value in same table
Another thing you may want to try then is instead of appending the two data tables, merge them side by side.
So intead of your original table, you end up with something like this:
CODE PREV_STATE CUR_STATE CHANGED 1015 A A TRUE 1016 A B FALSE 1017 B B TRUE If whatever index you're using for each row stays the same between days, this may be a better way to store your data. Be sure to check my previous reply for DAX code to solve your original problem
Though I'm not sure why you want this as a PowerQuery function instead of as a DAX, but it's possible.
Go into the Query Editor, and at the far left of the Transform tab, you should see a Group By button. Go through that wizard and Group By both Code and State. Here's the entirety of the Power Query I used to create the results table as you have with the data snippet provided, with the bolded section being the important one:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwNFXSUXJUitUBc8yQOeZAjhOMY4rMMUPmYChzRuagmxYLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Code = _t, State = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", Int64.Type}, {"State", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Code", "State"}, {{"Result", each Table.RowCount(_), type number}})
in
#"Grouped Rows"You should be able to copy/paste the above into the advanced editor and play with it directly.
Cmcmahan, I did it, but I lost the another columns, I want to keep them (in the example bottom I didn´t show them).
PD: I suggested Power Query for better performance, (I think). If there is another way to achieve this without impacting performance, feel free to help me.
So the answer depends on what data is in those other columns. If it's data that would make sense to aggregate (either through COUNT, SUM, AVERAGE, etc) you can add it in the Group By wizard, or with the advanced Power Query editor like so:
#"Grouped Rows1" = Table.Group(#"Changed Type", {"Code", "State"}, {{"Result", each Table.RowCount(_), type number}, {"Highest Price", each List.Max([Price]), type text}})If you do not have data you want aggregated, you need to create a separate table and then do the Group By on that like before.
The problem with Power Query is that there isn't a way to dynamically copy another table. When you press copy, it copies a snapshot of the current table, so if you upload new data, you have to copy the table over again, or set the new table to load from the same data source.
I'm still not sure why you would strongly prefer Power Query instead of DAX for this. Creating a custom summary table is incredibly easy in DAX, and I feel like if you have millions of rows of data, having to copy and then do Power Query transformations on the second table seems like a ton of extra processing.
I tried to search and see if there are performance differences for Power Query vs DAX, and can't find any information on that. And since DAX is built to create measures exactly like this, I would use that.
Ok, I understand, could you give me a solution using DAX? Honestly, I got lost in your explanation. If you think It's possible with DAX, go ahead.
Maybe if I explain my scenario, it would be better for you.
I got 2 csv with the same structure, one of them has the transactions of yesterday, others of today.
I made a query, whose data source is a folder that contains these files, and creates a single table identifying Previous Data vs. Actual Data in a column. What I need is to easily filter the codes where the state changes from one period to another. And therefore, I thought about creating a column that would do the unique count of the states for each code using a filter object or a graph.
Please see the below M code for Custom Column
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwNFXSUXJUitUBc8yQOeZAjhOMY4rMMUPmYChzRuagmxYLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Code = _t, State = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", Int64.Type}, {"State", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let c = [Code], s = [State] in Table.RowCount( Table.SelectRows( #"Changed Type", each [Code] = c and [State] = s ) ), Int64.Type) in #"Added Custom"Regards,
Mariusz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.It's not working. The query generates (loading here) a data processing of more than 3 gb.
