Building a table inside of a cell - schedule view

[Anonymous]

Hello,

In Power Bi, would anyone know how to build a table inside a of cell that looks like this? 

Data: 

Meeting Date	Meeting Type	Meeting time start	Meeting time end	Sessions
6/2/20	keynote	2:00 PM	5:00 PM	2:00 PM - 3:00 PM Cats are great Mewton Inform 3:00 PM - 4:00 PM 101 on cats eyes Lasic Discuss 4:00 PM - 5:00 PM Cat hugs pawsome Interaction
6/3/20	Guest Speaker	5:00 PM	6:00 PM	5:00 PM - 5:30 PM Speaker Mr. Grumpy Cat Inform 5:30 PM - 6:00 PM Q&A Mr. Grumpy Cat Interaction

Desired outcome:

I tried to do it in a matrix visual but wasn't able to.

Thank you!!

L

[Anonymous]

Thank you!

[edhans · Accepted solution]

You cannot do that directly. A few issues.

1. You have what looks like a merged cell in Excel where the first cell is 3 rows high. You cannot do that in Power Query or DAX. Tables are records and fields. No merging.
2. There is no way I can tell in your "Session" field to determine what a speaker is vs what a title is. They all have different word counts and nothing to signify when one starts and another ends. It is all just spaces, and spaces separate part of a person's name, so that isn't helpful.

That said, this is a start. It returns the below table which can then be loaded into the DAX model as a table.

Full M code below. Forgive my horrible step names. Didn't bother fixing them. I'd never put uncommented and uselessly named code like this into production, but hopefully it will help you.

As said, I know of no way to figure out the difference between a topic and a speaker name due to there being no consistency - same word count, special delimiter, etc.

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY/NisIwFIVf5ZB1tWmrXcxuGEEEC4rL6iKUO04pbUpugvTtjZ1MdaYMZPERzs89ZSnyOI1TKSLR0NBpS57SNylxKDytJwp/WCAL9KEsQxnC1ZCyKOhmdYdd96lNKy5RKbLJsgqUyAReUz2cNBBjr7iusKm5csy+5fkeAaspYP3sxJe7Mnp1Y92Sr7NkVGVr3c3seZx9L9s6YotTT6oh82tV/nfp2JWNFPQozBJb49p+GOtfBv4oFwg5OJ6dlGn+Pjf9c+blDg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Meeting Date" = _t, #"Meeting Type" = _t, #"Meeting time start" = _t, #"Meeting time end" = _t, Sessions = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Meeting Date", type text}, {"Meeting Type", type text}, {"Meeting time start", type time}, {"Meeting time end", type time}, {"Sessions", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "New Session", each if [Sessions] = "" then [Meeting Date] else [Sessions], type text),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "New Date", each if [Sessions] = "" then null else [Meeting Date], type date),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"New Date", "Meeting Type", "Meeting time start", "Meeting time end", "New Session"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Other Columns","",null,Replacer.ReplaceValue,{"Meeting Type"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"New Date", "Meeting Type", "Meeting time start", "Meeting time end"}),
    #"Added Custom2" = Table.AddColumn(#"Filled Down", "Sessions", each Text.Start([New Session],18), type text),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Purpose", 
        each 
            Text.AfterDelimiter([New Session]," ",
                Text.Length([New Session]) -
                Text.Length(
                Text.Replace([New Session]," ", "")
            )-1
), type text),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom3",{{"New Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"New Date", "Meeting Type", "Meeting time start", "Meeting time end"}, {{"All Rows", each _, type table [New Date=date, Meeting Type=text, Meeting time start=time, Meeting time end=time, New Session=text, Sessions=text, Purpose=text]}}),
    #"Added Custom4" = Table.AddColumn(#"Grouped Rows", "Custom",
        each 
            let varList = [All Rows][Sessions] 
        in Text.Combine(List.Transform(varList, Text.From), "#(lf)")),
    Custom1 = Table.AddColumn(#"Added Custom4", "Custom2",
        each 
            let varList = [All Rows][Purpose] 
        in Text.Combine(List.Transform(varList, Text.From), "#(lf)")),
    #"Removed Other Columns1" = Table.SelectColumns(Custom1,{"New Date", "Meeting Type", "Meeting time start", "Meeting time end", "Custom", "Custom2"})
in
    #"Removed Other Columns1"