Need help with data consolidation

[iamhere_18]

Hi All!

I could use some help with this, if anyone has any ideas on how to tackle it I'd be really grateful. Here's an example of what I'm working with:

ID	Location	Contact
1	123 Main St
1		Jane Doe
1		Jack Frost
2	456 Second St
2
2		Lacie Lou
2		John Doe
3	789 Third St
3

<tbody>
</tbody>

My files have hundreds of rows and atleast 20 columns so this is a much smaller example obviously.. How can I condense something like this without corrupting the data? Is it something that can only be done manually?

 

[sandy666]

You didn't show what you want to achieve but maybe...

with your example & PowerQuery:

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filled Down" = Table.FillDown(Source,{"Location"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"ID", "Location"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Contact", each List.Distinct(Table.Column([Count],"Contact"))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Contact", each Text.Combine(List.Transform(_, Text.From), ", "), type text})
in
    #"Extracted Values"[/SIZE]

ID	Location	Contact		ID	Location	Contact
1​	123 Main St			1​	123 Main St	Jane Doe, Jack Frost
1​		Jane Doe		2​	456 Second St	Lacie Lou, John Doe
1​		Jack Frost		3​	789 Third St
2​	456 Second St
2​
2​		Lacie Lou
2​		John Doe
3​	789 Third St
3​

 

[iamhere_18]

Thanks for your response! Here is an example of what I need achieved:

Some of the rows need a blank contact cell, they might have info in a different column (ie: accountspayable@somebusiness.com in the email column not pictured in this example)

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

Id	Location	Contact
1	123 Main St
1	123 Main St	Jane Doe
1	123 Main St	Jack Frost
2	456 Second St	Lacie Lou
2	456 Second St	John Doe
3	789 Third St

<colgroup><col style="width: 100px"><col width="100"><col width="100"></colgroup><tbody>
</tbody>

 

[sandy666]

something like this?

ID	Location	Contact
1​	123 Main St
1​	123 Main St	Jane Doe
1​	123 Main St	Jack Frost
2​	456 Second St
2​	456 Second St	Lacie Lou
2​	456 Second St	John Doe
3​	789 Third St

 

[iamhere_18]

That would work! Any thoughts on how to achieve this?

 

[sandy666]

sure

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filled Down" = Table.FillDown(Source,{"Location"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"ID", "Location"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Contact", each List.Distinct(Table.Column([Count],"Contact"))),
    #"Expanded Contact" = Table.ExpandListColumn(#"Added Custom", "Contact")
in
    #"Expanded Contact"[/SIZE]

 

[iamhere_18]

I'll give that a go! Thank you!

 

[sandy666]

You are welcome