Users places visited

[Anonymous]

Hi, I have some data that I want to change. I do not know what to use (Desktop or Query). I need some help for the next: 

Data: 

CheckIn	User	Place
01/01/2020	A	MEX
01/01/2020	B	MEX
02/01/2020	C	EUA
02/01/2020	A	CAN
02/01/2020	B	EUA
04/01/2020	B	MEX
05/01/2020	C	EUA
05/01/2020	A	BOL
06/01/2020	B	CAN

Result: 

User	Co1	Col2	Col3
A	MEX	CAN	BOL
B	MEX	EUA	CAN
C	EUA

Thank you. 

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CheckIn", type date}, {"User", type text}, {"Place", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"CheckIn", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"CheckIn"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
    Partition = Table.Group(#"Removed Duplicates", {"User"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Place", "Index"}, {"Place", "Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Partition", {{"Index", type text}}, "en-IN"), List.Distinct(Table.TransformColumnTypes(#"Expanded Partition", {{"Index", type text}}, "en-IN")[Index]), "Index", "Place")
in
    #"Pivoted Column"

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CheckIn", type date}, {"User", type text}, {"Place", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"CheckIn", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"CheckIn"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
    Partition = Table.Group(#"Removed Duplicates", {"User"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Place", "Index"}, {"Place", "Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Partition", {{"Index", type text}}, "en-IN"), List.Distinct(Table.TransformColumnTypes(#"Expanded Partition", {{"Index", type text}}, "en-IN")[Index]), "Index", "Place")
in
    #"Pivoted Column"

Hope this helps.

[Anonymous]

@Ashish_Mathur what can I do with a csv file? 

[mwegener · Accepted solution]

Hi @Anonymous ,

In the appendix you will find the solution with a CSV source.

[mwegener]

Hi @Anonymous ,

if my solution helped you, please mark my post as a solution.

[mwegener]

Hi @Anonymous ,

it should also work with a CSV file.
Can you share the CSV?

[Anonymous]

Thank you. It works. 

When I try to do the same with a csv, it is not possible. 

What Can I do?. 

[mwegener]

Really @Ashish_Mathur ?
Can you please explain to me what the improvement or edvantage to my answer is?

The steps look pretty identical to me.

[Ashish_Mathur]

I did not check your answer before posting mine.

[mwegener]

Maybe next time.

[mwegener]

Hi @Anonymous 

take a look at the attached PBIX file.

Here you can find some information about the most important steps.

https://www.excelguru.ca/blog/2018/06/27/number-rows-by-group-using-power-query/

[Greg_Deckler]

@ImkeF