Forum Discussion
rjsidek
Helper II
Helper IICreating a Summary Table from an existing table
Hi everyone, I have a sample dataset that looks a bit like this
I want to create summary table that shows all the entries of a company in one single row. Ideally, I would have one row for each company. I tried to use summariza by max date, but certain entries won't be captured. For example, for company "a", if I summarized by max date, the only column that would have an entry is the status column and everything else would be empty. The final output table that I would want looks something like this:
Can I achieve this with PowerQuery? Any help is appreciated.
Hello rjsidek,
Please try following M query code :
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZBfC4IwFMW/iuw52N1MykfpD/QSgb1JD7YGDmyG0z5/904zTSi43N2xwzm/3SxjOVswkFxILkHEeDnp2lU2EDgKAOyS2rGqmyJInCH9pjDWn5V17V3XOCaqMU/NLoveMRo5diWjYaQi5ZUyVvNsiWMEHgBbWrWf6IO9+XOPBFaZvAxSXT+N0m4KcfXgHARa+w90JSYQ39AxBznVhzCDVqRccwjfyh469Il+YbS7JN0lR3otTFmah7Ge8KxVMQ0mOxGP7Ppg8QNUrmf6cW11fhuWgBuA5T/t5QU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Company = _t, Date = _t, #"Sponsor Name" = _t, #"Valuation (in Millions)" = _t, #"Deal Size (in Millions)" = _t, Geography = _t, Country = _t, Sector = _t, Status = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company", type text}, {"Date", type date}, {"Sponsor Name", type text}, {"Valuation (in Millions)", Int64.Type}, {"Deal Size (in Millions)", Int64.Type}, {"Geography", type text}, {"Country", type text}, {"Sector", type text}, {"Status", type text}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Company", Order.Ascending}, {"Date", Order.Ascending}}), #"Replaced Value" = Table.ReplaceValue(#"Sorted Rows","",null,Replacer.ReplaceValue,{"Sponsor Name", "Geography", "Country", "Sector", "Status"}), #"Filled Down" = Table.FillDown(#"Replaced Value",{"Sponsor Name", "Valuation (in Millions)", "Deal Size (in Millions)", "Geography", "Country", "Sector", "Status"}), #"Grouped Rows" = Table.Group(#"Filled Down", {"Company"}, {{"Date", each List.Max([Date]), type date}, {"Sponsor Name", each List.Max([Sponsor Name]), type text}, {"Valuation (in Millions)", each List.Max([#"Valuation (in Millions)"]), type number}, {"Deal Size (in Millions)", each List.Max([#"Deal Size (in Millions)"]), type number}, {"Geography", each List.Max([Geography]), type text}, {"Country", each List.Max([Country]), type text}, {"Sector", each List.Max([Sector]), type text}, {"Status", each List.Max([Status]), type text}}) in #"Grouped Rows"Hope this is what you want.
3 Replies
rajulshahResident Rockstar
Hello rjsidek,
Please try following M query code :
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZBfC4IwFMW/iuw52N1MykfpD/QSgb1JD7YGDmyG0z5/904zTSi43N2xwzm/3SxjOVswkFxILkHEeDnp2lU2EDgKAOyS2rGqmyJInCH9pjDWn5V17V3XOCaqMU/NLoveMRo5diWjYaQi5ZUyVvNsiWMEHgBbWrWf6IO9+XOPBFaZvAxSXT+N0m4KcfXgHARa+w90JSYQ39AxBznVhzCDVqRccwjfyh469Il+YbS7JN0lR3otTFmah7Ge8KxVMQ0mOxGP7Ppg8QNUrmf6cW11fhuWgBuA5T/t5QU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Company = _t, Date = _t, #"Sponsor Name" = _t, #"Valuation (in Millions)" = _t, #"Deal Size (in Millions)" = _t, Geography = _t, Country = _t, Sector = _t, Status = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company", type text}, {"Date", type date}, {"Sponsor Name", type text}, {"Valuation (in Millions)", Int64.Type}, {"Deal Size (in Millions)", Int64.Type}, {"Geography", type text}, {"Country", type text}, {"Sector", type text}, {"Status", type text}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Company", Order.Ascending}, {"Date", Order.Ascending}}), #"Replaced Value" = Table.ReplaceValue(#"Sorted Rows","",null,Replacer.ReplaceValue,{"Sponsor Name", "Geography", "Country", "Sector", "Status"}), #"Filled Down" = Table.FillDown(#"Replaced Value",{"Sponsor Name", "Valuation (in Millions)", "Deal Size (in Millions)", "Geography", "Country", "Sector", "Status"}), #"Grouped Rows" = Table.Group(#"Filled Down", {"Company"}, {{"Date", each List.Max([Date]), type date}, {"Sponsor Name", each List.Max([Sponsor Name]), type text}, {"Valuation (in Millions)", each List.Max([#"Valuation (in Millions)"]), type number}, {"Deal Size (in Millions)", each List.Max([#"Deal Size (in Millions)"]), type number}, {"Geography", each List.Max([Geography]), type text}, {"Country", each List.Max([Country]), type text}, {"Sector", each List.Max([Sector]), type text}, {"Status", each List.Max([Status]), type text}}) in #"Grouped Rows"Hope this is what you want.
