cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
davidhynes Frequent Visitor
Frequent Visitor

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
v-cherch-msft Super Contributor
Super Contributor

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

Hi @davidhynes 

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,

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Super User
Super User

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

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



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





davidhynes Frequent Visitor
Frequent Visitor

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

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 A100137
Group B100214

 

Cheers,
Dave

davidhynes Frequent Visitor
Frequent Visitor

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

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!

v-cherch-msft Super Contributor
Super Contributor

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

Hi @davidhynes 

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,

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

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

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 127 members 1,514 guests
Please welcome our newest community members: