Help with consolidating multiple rows for same person

[Clyde]

Hello, 👋 

First time here, newbie at Power BI but a fast learner.

I am trying to optimize a report directly in power BI. I was able to use VBA to create the report I need and then import it into Power BI but I would like to be able just process the raw data directly in Power BI without having to run dynamic VBA macros.

The data is set up like this:

(Dashes are column separators)

An individual will do a function but the way the information is provided, if the individual took .6 hours to do something, the function will have children units for different unit of measurements. Instead of having a  single row with 9 columns, the way the report is generated it breaks it down into 3 rows of 5 columns each. I don't want to add up the times for the 3 different elements, it should be read only once for that name. Then, we should be able to pull the unit and the usage so we can visualize how long it takes name1 to process the Eaches, pallets, cases without summing up the 3 different times (we only need it once)

Date - Name - Element - Time - Unit - Usage

Oct 30 - Name 1 - Eaches - .6 - 400 - .4

Oct 30 - Name 1 - Pallets - .6 - .5 - .4

Oct 30 - Name 1 - Cases - .6 - 7 - .4

It should be:

Oct 30 - Name1 - .6(Time) - 400(Eaches) - .5(pallets) - 7(cases) - .4(usage)

Please let me know what else would be needed to figure out or let me know if you have any additional questions. I will also appreciate being pointed in the right direction, advice, or tips. I am more than happy to answer any questions. Appreciate you taking the time to read through my post!

[MFelix · Accepted solution]

Hi @Clyde ,

Select the Column element the do a transform and Pivot columns where the unit are used in values:

Final resul will be what you need.

Check complete code here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjbQNzTQNzIwMlbSUfJLzE1VMAQyXBOTM1KLgQw9MyBhYmAAYpooxerg0hCQmJOTWgLXoWdKSINzYnEqTLU5VHEsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Name = _t, Element = _t, Time = _t, Unit = _t, Usage = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Name", type text}, {"Element", type text}, {"Time", type number}, {"Unit", type number}, {"Usage", type number}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Element]), "Element", "Unit", List.Sum)
in
    #"Pivoted Column"