Creating a Summary Table from an existing table

[rjsidek]

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.

[rajulshah · Accepted solution]

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.

[rjsidek]

Hi Rajul,

Sorry but I am rather new here on PowerBI. How would I add this code on query editor? I am not sure I know how.

[rajulshah]

@rjsidek,

Please go to below link. It shows how to achieve it.

Video

Hope this helps.