Is it possible to Split the Amount column with group by condition of middle 3 columns as per rundate column

[chakkrav]

Source data

rundate	entity	leaf_level_id	record_type	Amount
31/03/2020	J11	L2_DEDASS	CA	-11
30/09/2019	J11	L2_DEDASS	CA	-9

Expected Result set

entity	leaf_level_id	record_type	31/03/2020	30/09/2019
J11	L2_DEDASS	CA	-11	-9

 

[Chihiro]

Sure it's possible. But I would do it in Pivot Table.

1. Load data to PQ => Connection only & Load to Data Model
2. Add Pivot from data model.
3. Add rundate to column field. This will auto group by Year, Quarter, Month. Leave Year & Quarter but remove others.
4. Add entity to cell_name in row label. Add amount into value field.
5. Set pivot style to Tabular form and repeat item labels.

See attached.

 

[chakkrav]

Sure it's possible. But I would do it in Pivot Table.

1. Load data to PQ => Connection only & Load to Data Model
2. Add Pivot from data model.
3. Add rundate to column field. This will auto group by Year, Quarter, Month. Leave Year & Quarter but remove others.
4. Add entity to cell_name in row label. Add amount into value field.
5. Set pivot style to Tabular form and repeat item labels.

See attached.

Thanks Chihiro & appreciate the response. I'm aware of the Pivot solution. I was looking for Power query solution, as more logics needs to be inbuilt / processing required once the data is fetched as per expected result set in power query.

 

[AlanSidman]

Maybe this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"As Of Date", type date}, {"version", Int64.Type}, {"rundate", type date}, {"entity", type text}, {"leaf_level_id", type text}, {"element_id", type text}, {"record_type", type text}, {"source_from", type text}, {"cell_name", type text}, {"Amount", type number}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"As Of Date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"As Of Date", type text}}, "en-US")[#"As Of Date"]), "As Of Date", "Amount", List.Sum),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"version", "rundate"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"record_type", "entity", "leaf_level_id", "element_id", "source_from", "cell_name"}, {{"Current Qtr", each List.Sum([#"3/31/2020"]), type nullable number}, {"Last Qtr", each List.Sum([#"9/30/2019"]), type nullable number}})
in
    #"Grouped Rows"

 

[chakkrav]

Thanks a ton for the suggested solution. So "Pivot a column" along with "group by" steps worked the magic