Hey guys,
I’m analysing the balance sheet, P&L and so on of a company.
I would like to have the accounts grouped something like this:
I really don’t know what’s the best solution to have this like that.
Let me send you a demo of the file
accounting.xlsx (12.6 KB)
I’ve tested in power query but didn’t manage to do has i want.
Can you guys just point me the direction or let me know the best solution for it?
Thanks a lot
Pedro
Hi @pedroccamara,
If you only have to achieve the hierarchy as you have shown in the picture then you can use the below query the output will be as shown. You can add 1 step more if you want to change the type of column.
If you have more hierarchy then you can follow the same logic to add steps before last step.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"GroupingCode", Int64.Type}}),
#"Calculated Text Length" = Table.TransformColumns(Table.TransformColumnTypes(#"Changed Type", {{"GroupingCode", type text}}, "en-US"),{{"GroupingCode", Text.Length, Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Calculated Text Length", " ", each if [GroupingCode] = null then [AccountID] else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", " ", each if [#" "] <> null then [AccountDescription] else if [GroupingCode] = 2 then [AccountID] else null),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", " ", each if [#" "] = null and [#" "] <> null then [AccountDescription] else if [GroupingCode] = 3 then [AccountID] else null),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", " ", each if [#" "] = null and [#" "] = null and [#" "] <> null then [AccountDescription] else null),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom3",{" ", " ", " ", " ", "Open Bal", "Close Bal", "Transactions"})
in
#"Removed Other Columns"
1 Like
Guy, although i was expecting some solution with hierarchies, which i don’t know how, @MK3010 already gave me the m code and i’m not sure but it looks same as mine and couldn’t do the matrix like that. But you @AntrikshSharma add a step different than mine, the 9th, fill down. I believe it makes all the difference, but i’m not sure.
As soon as i get home i will take a look at it.
Thanks a lot guys
P.S. To be more safe, the condition column should have been made by the grouping category and not Acc length, because at a 3 numbers level, some are aggregation (GA) and others are movement (GM) but none are reason (GR) that’s for 2 digits accounts, period.
If we use GroupingCategory column as condition then the number of steps will reduce further as below.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source , " ", each if [GroupingCategory] = "GR" then [AccountID] else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", " ", each if [#" "] <> null then [AccountDescription] else if [GroupingCategory] = "GA" then [AccountID] else null),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", " ", each if [#" "] = null and [#" "] <> null then [AccountDescription] else if [GroupingCategory] = "GM" then [AccountID] else null),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", " ", each if [#" "] = null and [#" "] = null and [#" "] <> null then [AccountDescription] else null),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom3",{" ", " ", " ", " ", "Open Bal", "Close Bal", "Transactions"})
in
#"Removed Other Columns"
1 Like
Hey @MK3010
Thank you for your answer. Still don’t know if your answer will help me because i couldn’t add it to my main query.
Adding the 2nd line was easy, it was just a simple add conditional column. After that, error. Maybe becasue of this, each if [#" "], which i don’t have it in my table.
Would it be possible for you to explain me the added custom1, 2 and 3, so i can do it here?
Thanks a lot
HI @pedroccamara,
The above solution which I gave is for excel so if we both are on same page then below is the explanation:-
Here we are creating blank column name i.e. first with 1 white space and 2nd as 2 and then 3rd as 3…
- First you add some dummy name while adding custom column and click ok.
- Goto formula bar with new step selected and edit the name with a single white space.
- Repeat the same step but this time with 2 white spaces and again so on…
For power BI you can use below which is Ankit’s solution with little change. hope this helps you please refer pbix file by Ankit.
let
Source = Excel.Workbook(File.Contents("C:\Users\Downloads\accounting.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Added Custom" = Table.AddColumn(Table1_Table, "Level 1", each if [GroupingCategory] = "GR" then [AccountID] else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Level 2", each if [Level 1] <> null then [AccountDescription] else if [GroupingCategory] = "GA" then [AccountID] else null),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Level 3", each if [Level 1] = null and [Level 2] <> null then [AccountDescription] else if [GroupingCategory] = "GM" then [AccountID] else null),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Level 4", each if [Level 1] = null and [Level 2] = null and [Level 3] <> null then [AccountDescription] else null),
#"Filled Down" = Table.FillDown(#"Added Custom3",{"Level 1", "Level 2"}),
#"Sorted Rows" = Table.Sort(#"Filled Down",{{"Level 3", Order.Descending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Level 3 Index", 1, 1, Int64.Type),
#"Added Custom4" = Table.AddColumn(#"Added Index", "Level 3 Sort", each if [Level 3] = null then null else [Level 3 Index]),
#"Sorted Rows1" = Table.Sort(#"Added Custom4",{{"Level 4", Order.Descending}}),
#"Added Index1" = Table.AddIndexColumn(#"Sorted Rows1", "Level 4 Index", 1, 1, Int64.Type),
#"Added Custom5" = Table.AddColumn(#"Added Index1", "Level 4 Sort", each if [Level 4] = null then null else [Level 4 Index]),
#"Sorted Rows2" = Table.Sort(#"Added Custom5",{{"AccountID", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows2",{"Level 3 Index", "Level 4 Index"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Company", type text}, {"File Date", type date}, {"AccountID", Int64.Type}, {"AccountDescription", type text}, {"GroupingCategory", type text}, {"GroupingCode", Int64.Type}, {"Open Bal", type number}, {"Close Bal", type number}, {"Transactions", type number}, {"Acc Length", Int64.Type}, {"Level 1", Int64.Type}, {"Level 2", type text}, {"Level 3", type text}, {"Level 4", type text}, {"Level 3 Sort", Int64.Type}, {"Level 4 Sort", Int64.Type}})
in
#"Changed Type"
1 Like
Guys,
Thank you very much for all your help.
It took me a while to understand my data wouldn’t work with all of those queries but it turns out, my data was wrong, but also another query “merge” table.
Thank you very very much all of you
1 Like