Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Users places visited

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 UserPlace
01/01/2020AMEX
01/01/2020BMEX
02/01/2020CEUA
02/01/2020ACAN
02/01/2020B

EUA

04/01/2020BMEX
05/01/2020CEUA
05/01/2020ABOL
06/01/2020BCAN

 

 

 

Result: 

 

UserCo1Col2Col3
AMEXCANBOL
BMEXEUACAN
CEUA  

 

 

 

Thank you. 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    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.

11 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    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.

    • mwegener's avatar
      mwegener
      Icon for Most Valuable Professional rankMost Valuable Professional
      6 years ago

      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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you. It works. 

       

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

      What Can I do?.