Forum Discussion
Solved
How to group values in two groups-power query
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
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.
3 Replies
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.
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.
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%
