How to group values in two groups-power query

[RAKESH1986]

Hi all,

i have these value i column, and i want to group these values in two group and take sum of another column values.

i want to group all red highligted in to one group and project management in another group

discipline, early_aw_wk, aw_early_baseline

Group 1, aw01, sum of values

project management, aw01, sum of values

Group 1, aw02, sum of values

project management, aw02, sum of values

Group 1, aw03, sum of values

project management, aw03, sum of values

could u tell me simplest way to achive this?

thanks

rakesh

[AlB · Accepted solution]

@RAKESH1986 

See it all at work in the attached file. Place the following 3 pieces of M code in a blank query each to see the steps

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZTdbsMgDIVfBUXaXTUB2U9vEfJapgAVOK2mro+x919RUBtTQnOVi/PJxzbHOZ+7Q/AaYuw2nTpxcf3wVy5fusumkOQkvVWkfpI+s2QOMBgH1YpEyyX7mpZrvt/svkEjs8qpHVhwSKrzNjX5CNGmJsdt7saC3itntBpYRIVGE0PRhCSZeQHqyfAzKPhEud0TQ4rJddV68ogIA2hv7ZhQNN7VLBcgargAUTvjIoYxLfzRrG8gksSrikxGIucgNYMhjV/zKFVJklaqeYQtiT2L6gvwp7auGlHkvELQRV0voXx/+ajQvucKvUdtjmZgv3+cyw+WdqdxDMVq5AqU+jVR2sAsjfujenZId2TxjO4I3ZtVCMGoW1vah+BjGTW5Ci4Oqg3PflaXfw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Discipline = _t, EARLY_AW_WK = _t, AW_EARLY_BASELINE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Discipline", type text}, {"EARLY_AW_WK", type text}, {"AW_EARLY_BASELINE", Percentage.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Discipline] = "PROJECT MANAGEMENT")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Discipline", "EARLY_AW_WK"}, {{"AW_EARLY_BASELINE", each List.Sum([AW_EARLY_BASELINE]), type nullable number}})
in
    #"Grouped Rows"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZTdbsMgDIVfBUXaXTUB2U9vEfJapgAVOK2mro+x919RUBtTQnOVi/PJxzbHOZ+7Q/AaYuw2nTpxcf3wVy5fusumkOQkvVWkfpI+s2QOMBgH1YpEyyX7mpZrvt/svkEjs8qpHVhwSKrzNjX5CNGmJsdt7saC3itntBpYRIVGE0PRhCSZeQHqyfAzKPhEud0TQ4rJddV68ogIA2hv7ZhQNN7VLBcgargAUTvjIoYxLfzRrG8gksSrikxGIucgNYMhjV/zKFVJklaqeYQtiT2L6gvwp7auGlHkvELQRV0voXx/+ajQvucKvUdtjmZgv3+cyw+WdqdxDMVq5AqU+jVR2sAsjfujenZId2TxjO4I3ZtVCMGoW1vah+BjGTW5Ci4Oqg3PflaXfw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Discipline = _t, EARLY_AW_WK = _t, AW_EARLY_BASELINE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Discipline", type text}, {"EARLY_AW_WK", type text}, {"AW_EARLY_BASELINE", Percentage.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Discipline] <> "PROJECT MANAGEMENT")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"EARLY_AW_WK"}, {{"AW_EARLY_BASELINE", each List.Sum([AW_EARLY_BASELINE]), type nullable number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Discipline", each "Group 1"),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Discipline", "EARLY_AW_WK", "AW_EARLY_BASELINE"})
in
    #"Reordered Columns"

let
    Source = Table.Combine({T1, T2}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Discipline", type text}, {"AW_EARLY_BASELINE", Percentage.Type}})
in
    #"Changed Type"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[RAKESH1986]

@AlB  thanks for response. this data i have in table-

Discipline	EARLY_AW_WK	AW_EARLY_BASELINE
PROCESS	AW01	0.02%
PROCESS	AW02	0.04%
PROCESS	AW03	0.07%
PIPELINE	AW01	0.02%
PIPELINE	AW02	0.03%
PIPELINE	AW03	0.05%
PROJECT MANAGEMENT	AW01	0.00%
PROJECT MANAGEMENT	AW02	0.11%
PROJECT MANAGEMENT	AW03	0.83%
MECHANICAL STATIC	AW01	0.01%
MECHANICAL STATIC	AW02	0.02%
MECHANICAL STATIC	AW03	0.03%
MECHANICAL ROTATING	AW01	0.01%
MECHANICAL ROTATING	AW02	0.03%
MECHANICAL ROTATING	AW03	0.04%
TELECOMMUNICATION	AW01	0.01%
TELECOMMUNICATION	AW02	0.03%
TELECOMMUNICATION	AW03	0.04%
INSTRUMENTATION	AW01	0.03%
INSTRUMENTATION	AW02	0.07%
INSTRUMENTATION	AW03	0.10%
ELECTRICAL	AW01	0.03%
ELECTRICAL	AW02	0.05%
ELECTRICAL	AW03	0.08%
PROCESS SAFETY	AW01	0.01%
PROCESS SAFETY	AW02	0.03%
PROCESS SAFETY	AW03	0.04%
PIPING	AW01	0.02%
PIPING	AW02	0.05%
PIPING	AW03	0.07%
CIVIL & STRUCTURAL	AW01	0.02%
CIVIL & STRUCTURAL	AW02	0.05%
CIVIL & STRUCTURAL	AW03	0.07%
MECHANICAL HVAC	AW01	0.01%
MECHANICAL HVAC	AW02	0.02%
MECHANICAL HVAC	AW03	0.04%
MATERIAL & CORROSION	AW01	0.02%
MATERIAL & CORROSION	AW02	0.03%
MATERIAL & CORROSION	AW03	0.05%

[AlB]

Hi @RAKESH1986 

Just split the initial table in two. On one you keep Project Management, on the other one all the rest. Then, for both tables, group by discipline and early_aw_wk, adding the results.

Finally, just append both tables to create the final output

If you can share a sample of the data in text-tabular format, so that the contents can be copied, I can build the query but it should be straightforward with the steps above

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.