Tanspose/pivot dataset to get columns instaed of rows

[Kristofferaabo]

Hi I have a task where I need to calculate days between two 'meetings'. Normally the data I get have this in columns and there wont be a problem. 

Is there any wat I can transpose/pivot this so I can do this easier?

I have attached an example of the data

Thanks in advance

Kristoffer

[ricardocamargos]

Hi @Kristofferaabo,

How are you gonna calculate it ?

If u use your dataset (attached image) u can use the calculate function to do it.

However I'm not sure if u can do it dinamically unless you link which meeting you wanna compare in a table.

Ricardo

[Kristofferaabo]

So I would like to calculate like this:

days(meeting 1.date) - days(meeting 2.date)

Does this make sense? 

[ricardocamargos]

@Kristofferaabo,

Can you provide the visual you would like to build ?

Which context would be relevant for days(meeting 1.date) - days(meeting 2.date) ?

Tks,

Ricardo

[Kristofferaabo]

Best case I would need it as a column (delta betwwen two meetings) that way I can anaylyze time gaps across the various persons and roll it up to country

[ricardocamargos]

Hi @Kristofferaabo,

Sorry, I can't visualize it. 

Can you, please, try a demo on Excel ? So I can see how you want visualize your data.

Thanks,

 Ricardo

[Kristofferaabo]

thanks for the patience.

Something like this

[parry2k]

@Kristofferaabo your requirement is not clear.

Is it always K2-J2?

What about other dates?

[ricardocamargos · Accepted solution]

Hi @Kristofferaabo,

Click on "Edit Queries" -> "Advanced Editor" and paste this code:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc8xC4MwEAXgvxJuFnJXjdqxg6UgTnVQJKMUlw7V/4/v0kztbXnwveRlWWj4uIkKGtb12N4vF3CW2rP4C0uLMFEs/lSJM0MFVVrpLKV1Lr2wqgZhtJSoavOLNcLjq7Z9d/PPMtzGlTot3W2XtgXPVziuEJ62y+vSH5LrexumgU2G2rpRjCc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Person = _t, #"Type of Meeting" = _t, #"Date of Meeting" = _t, #"Place of Meeting" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}, {"Type of Meeting", type text}, {"Date of Meeting", type date}, {"Place of Meeting", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Place of Meeting"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Type of Meeting"]), "Type of Meeting", "Date of Meeting")
in
#"Pivoted Column"

After close and apply.

I tried it deleting duplicates (person with more than 1 meeting in the same day).

Thanks,

Ricardo