Split Columns by Delimiter and Reference Another Table in a Star Schema

[Anonymous]

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

[PattemManohar]

@Anonymous  Could you please post the sample data and expected output which will be really helpful to suggest an accurate solution.

[Anonymous]

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

[Anonymous]

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!

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-cherch-msft · Accepted 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,