Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PeeWhy
Frequent Visitor

Best Practice to Consolidate a Table with Multiple Values

I am having a hard time wrapping my head around the best way to merge this data and looking for a best practice approach.

 

Here is a sample of some raw data in an imported CSV.  There are some common columns and uncommon ones.

CUSTOMER

CITYSTATEINVWEIGHT
Customer AHOUSTONTX37
Customer AHOUSTONTX55
Customer BDALLASTX1023
Customer BDALLASTX1532

 

 

I  am trying to consolidate into a single summed-up table summing the value like you would in excel SUMIF.  I have tried making a distinct list of the customer names but It keeps saying I have a many to many relationship and unable to sum the values.

 

What is the best way to take the above imported data and end up with a consolidated table like the below that I can report on?

 

CUSTOMER

CITYSTATEINVWEIGHT
Customer AHOUSTONTX812
Customer BDALLASTX2555

 

 

1 ACCEPTED SOLUTION
jsaunders_zero9
Responsive Resident
Responsive Resident

Considering Grouping the rows in PowerQuery.

jsaunders_zero9_0-1651788518126.png

= Table.Group(#"Changed Type", {"CUSTOMER", "CITY", "STATE"}, {{"INV", each List.Sum([INV]), type nullable number}, {"WEIGHT", each List.Sum([WEIGHT]), type nullable number}})

 

View solution in original post

3 REPLIES 3
jsaunders_zero9
Responsive Resident
Responsive Resident

Considering Grouping the rows in PowerQuery.

jsaunders_zero9_0-1651788518126.png

= Table.Group(#"Changed Type", {"CUSTOMER", "CITY", "STATE"}, {{"INV", each List.Sum([INV]), type nullable number}, {"WEIGHT", each List.Sum([WEIGHT]), type nullable number}})

 

I have never done that before.  Thank you!  Would you still do that if it were a much larger table to clean up?  

Hi @PeeWhy 

if it were a much larger table, you can still use this way to clean up.

 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.