Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
Full disclosure, I haven't had a good look to see if there is an answer already because I wouldn't know how to structure the search to find it!
We have a large dataset of website usage metrics and to avoid exponential growth we have developed a star schema with the metrics table and reference tables hanging off of that. The metrics table has a column called group ID, in here there can be many ID's (4-5 digit numbers) separated by comma's, these ID's related to another table called group details which has two columns, the first is group ID, second is the group name. What I need to do is a count of events for each group name, I split the columns by a delimiter and tried a few things but didn't even get close.
Hope that made sense! Does anyone have any ideas I can try?
Cheers,
Dave
Solved! Go to Solution.
Hi @Anonymous
You may use 'Split columns' and 'Unpivot columns' for the data table.Then link the two tables.Please check the attached file.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bda9bqNAFIbhW4lcu+D8j+9juyjlFiulzP1rbWzxknyhGCG+A9I8nBl4f7/Ydj/scr38+ff1+fftcXa/ZNe3+xiXj+urwo8Kf1WQxZGFZHlk+e3Jj9HqMbpTXUd1yZP6yFqyObKRbB3Zkux2ZLefmW2YbL+j2InN5H7ETMgMMhMzw8xSQohMjAwjEyQDyUTJUDJhMphMnBwn3yREyEXITz0lQo6Qv4T2ntn2njH70TmOmYuZY+Zi5pi5mDlmLmaOmYuZY+ZiFpiFmAVm8TLLfea3feaLQvxC/OK0KKXDAq0QrUArRCvQCtEKtEK0Aq0QrUArRCvRyu8rcR9nZ/Enzt4Wxa1Y5tPSnuVNCYopioliimKe9jZRTBRTFBPFFMVEMUUxUUxRTBRTFAvFkp4rnErWaSFUIlQIlQgVQiVCddrsRagQKhEqhOol5OwKp3dbaJVoFVolWo1Wi1aj1aLVaLVoNVotWo1Wi1aj1aLVp2+jaDVaLf3UCLUINUItQoPQiNAgNCI0CI0IDUIjQoPQiNAgNCI0CI0Izen3QYQGoRGhQWhEaCG0RGghtERoIbREaCG0EPr4Dw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, title = _t, groups = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"title", type text}, {"groups", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "groups", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"groups.1", "groups.2", "groups.3", "groups.4", "groups.5", "groups.6"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"groups.1", Int64.Type}, {"groups.2", Int64.Type}, {"groups.3", Int64.Type}, {"groups.4", Int64.Type}, {"groups.5", Int64.Type}, {"groups.6", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"ID", "title"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}) in #"Removed Columns"
Count of Items = COUNT(Data[ID])
Regards,
@Anonymous Could you please post the sample data and expected output which will be really helpful to suggest an accurate solution.
Proud to be a PBI Community Champion
Thanks Pattem!
I've created a sample of data here, in the XLSX there is two tabs, a reference table tab, and a data table tab. The data table has a column called groups which indicates which groups the item belongs in. The reference table has group name and group_id as the names. The IDs in the groups (separated by commas) links to the group_id so you can reference the name.
Basically, as output I want to be able to see which group name in each item in the data table belongs to, easy if there is only one ID but I'm not skilled enough to work it out for multiple IDs.
As an example:
Group Name Group ID Count of Items in Group
Group A | 1001 | 37 |
Group B | 1002 | 14 |
Cheers,
Dave
The table didn't format properly but the table headers should be Group Bame, Group ID and Count of Items in Group.
Hope that all makes sense!
Hi,
You may download my PBI file from here.
Hope this helps.
Hi @Anonymous
You may use 'Split columns' and 'Unpivot columns' for the data table.Then link the two tables.Please check the attached file.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bda9bqNAFIbhW4lcu+D8j+9juyjlFiulzP1rbWzxknyhGCG+A9I8nBl4f7/Ydj/scr38+ff1+fftcXa/ZNe3+xiXj+urwo8Kf1WQxZGFZHlk+e3Jj9HqMbpTXUd1yZP6yFqyObKRbB3Zkux2ZLefmW2YbL+j2InN5H7ETMgMMhMzw8xSQohMjAwjEyQDyUTJUDJhMphMnBwn3yREyEXITz0lQo6Qv4T2ntn2njH70TmOmYuZY+Zi5pi5mDlmLmaOmYuZY+ZiFpiFmAVm8TLLfea3feaLQvxC/OK0KKXDAq0QrUArRCvQCtEKtEK0Aq0QrUArRCvRyu8rcR9nZ/Enzt4Wxa1Y5tPSnuVNCYopioliimKe9jZRTBRTFBPFFMVEMUUxUUxRTBRTFAvFkp4rnErWaSFUIlQIlQgVQiVCddrsRagQKhEqhOol5OwKp3dbaJVoFVolWo1Wi1aj1aLVaLVoNVotWo1Wi1aj1aLVp2+jaDVaLf3UCLUINUItQoPQiNAgNCI0CI0IDUIjQoPQiNAgNCI0CI0Izen3QYQGoRGhQWhEaCG0RGghtERoIbREaCG0EPr4Dw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, title = _t, groups = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"title", type text}, {"groups", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "groups", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"groups.1", "groups.2", "groups.3", "groups.4", "groups.5", "groups.6"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"groups.1", Int64.Type}, {"groups.2", Int64.Type}, {"groups.3", Int64.Type}, {"groups.4", Int64.Type}, {"groups.5", Int64.Type}, {"groups.6", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"ID", "title"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}) in #"Removed Columns"
Count of Items = COUNT(Data[ID])
Regards,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |