Pivot Table Help

[Lasantha]

Dear All,

Kindly look into the attached file. it contains unique value and deposit date of each unique. there are more than 1 deposit date for some unique. i need to show them in a separate columns.

example: unique 12937392014 has 4 deposit dates, so need to show them in a separate 4 column. is this possible with excel or pivot table. kindly please help me on this.

Thank you very much for your time.

Data file

Lasantha

 

[pecoflyer]

Please post your sheet on the forum so we can work on it

 

[Chihiro]

What version of Excel do you have? If you have 2010 or later I'd recommend using PowerQuery to add helper column.

Otherwise you'd need to add it via formula. Do note, with your data set, it will add significant overhead in performance.
Ex: ="Deposit"&COUNTIF($A$2:A2,A2)
Copy down.

You could add it via code, and avoid formula calculation overhead.

Add this to your column field and dates into value field as MIN/Max of.
FYI, I noticed that you have more than 4 Deposit dates in some of Uniq.

 

[Lasantha]

What version of Excel do you have? If you have 2010 or later I'd recommend using PowerQuery to add helper column.

Otherwise you'd need to add it via formula. Do note, with your data set, it will add significant overhead in performance.
Ex: ="Deposit"&COUNTIF($A$2:A2,A2)
Copy down.

You could add it via code, and avoid formula calculation overhead.

Add this to your column field and dates into value field as MIN/Max of.
FYI, I noticed that you have more than 4 Deposit dates in some of Uniq.

Hi,
I am using Excel 2013, can you please guide me how to do this with the PowerQuery.

and I have used above formula and its perfectly working. thank you very much for your time.

Lasantha

 

[Chihiro]

1. Load data to PQ. Make sure both columns are in text data type.
2. Group by [Unq], choose "All Rows" as aggregation (i.e. no aggregation) and name the aggregation column as "Temp".
3. Add custom column (DepositDate) with following formula.

=Text.Combine([Temp][DepositDate],",")

4. Split newly added [DepositDate] with comma as delimiter at each occurrence of the delimiter.
5. Remove [Temp] and load result to new worksheet.

Done.



Complete M for reference.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Uniq", type text}, {"DepositDate", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Uniq"}, {{"Temp", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "DepositDate", each Text.Combine([Temp][DepositDate],",")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "DepositDate", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"DepositDate.1", "DepositDate.2", "DepositDate.3", "DepositDate.4", "DepositDate.5", "DepositDate.6"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"DepositDate.1", type datetime}, {"DepositDate.2", type datetime}, {"DepositDate.3", type datetime}, {"DepositDate.4", type datetime}, {"DepositDate.5", type datetime}, {"DepositDate.6", type datetime}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Temp"})
in
    #"Removed Columns"