Use Index to Distinguish Data

Power Query
1

Hi

I have records where I want to distinguish between the first and second occurrences.
I have set out the data currently and what I want to end up with.

I was trying to use an index column and then assume that the records would stay in order and I would badge the first occurance for a Branch Income and the second Expense. There will only be two occrances per Branch.

thank you

Book100.xlsx (8.7 KB)

2

Hi @AllisterB,

Please watch below small video and it will find solution.

Below is the output of your file and then you can create column based on the value of Rank column.

image

3

@AllisterB,

@hafizsultan beat me to it by a few minutes, but I thought I’d post my solution anyway - same general approach:

image
image1010Ă—380 13.4 KB

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQpOzEksykwtBjINDQyUYnUwhI2gwk6owqbYhc1AwrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Branch = _t, Account = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Branch", type text}, {"Account", type text}, {"Value", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Index", "FullIndex"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"Branch"}, {{"Data", each _, type table [Branch=nullable text, Account=nullable text, Value=nullable number, FullIndex=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data], "Index", 1, 1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Branch", "Account", "Value", "FullIndex", "Index"}, {"Branch", "Account", "Value", "FullIndex", "Index"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Type", each if [Index] = 1 then "Income" else "Expense"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"FullIndex", "Index"})
in
    #"Removed Columns"

Full solution file posted below.

  • Brian

eDNA Forum - Income Expense Solution.pbix (17.5 KB)

1 Like
4

Thanks for that

I have attached code so far.

However what I really want to do in the Table.AddColumn is if on the First Occurance of Goods and Services Tax for a Branch I want to use Asset otherwise Liability. At present if a new Item Other Than Goods and Services Tax is imported then the Rank in the code might need to be 3 and >3.

let
Source = Excel.CurrentWorkbook(){[Name=“t_IMP_BSheetItems”]}[Content],
#“Changed Type” = Table.TransformColumnTypes(Source,{{“Source.Name”, type text}, {“BranchLine”, type text}, {“ThisYear”, Int64.Type}, {“LastYear”, Int64.Type}, {“SheetName”, type text}, {“SourceRowNumber”, Int64.Type}, {“Notes”, type any}}),
#“Reordered Columns” = Table.ReorderColumns(#“Changed Type”,{“Source.Name”, “SheetName”, “SourceRowNumber”, “BranchLine”, “ThisYear”, “LastYear”, “Notes”}),
#“Removed Other Columns” = Table.SelectColumns(#“Reordered Columns”,{“Source.Name”, “SheetName”, “BranchLine”, “ThisYear”, “LastYear”, “Notes”, “SourceRowNumber”}),
#“Reordered Columns1” = Table.ReorderColumns(#“Removed Other Columns”,{“Source.Name”, “BranchLine”, “ThisYear”, “LastYear”, “SheetName”, “SourceRowNumber”, “Notes”}),
#“Sorted Rows” = Table.Sort(#“Reordered Columns1”,{{“SourceRowNumber”, Order.Descending}}),
#“Grouped Rows” = Table.Group(#“Sorted Rows”, {“Source.Name”}, {{“Count”, each _, type table [Source.Name=text, BranchLine=text, ThisYear=number, LastYear=number, SheetName=text, SourceRowNumber=number, Notes=none]}}),
#“Added Custom” = Table.AddColumn(#“Grouped Rows”, “Custom”, each Table.AddIndexColumn([Count],“Rank”,1,1)),
#“Removed Other Columns2” = Table.SelectColumns(#“Added Custom”,{“Custom”}),
#“Expanded Custom” = Table.ExpandTableColumn(#“Removed Other Columns2”, “Custom”, {“Source.Name”, “BranchLine”, “ThisYear”, “LastYear”, “SheetName”, “SourceRowNumber”, “Notes”, “Rank”}, {“Source.Name”, “BranchLine”, “ThisYear”, “LastYear”, “SheetName”, “SourceRowNumber”, “Notes”, “Rank”}),
#“Added Custom1” = Table.AddColumn(#“Expanded Custom”, “GST A or L”, each if [BranchLine] = “Goods and Services Tax” and [Rank] = 2 then “Asset” else
if [BranchLine] = “Goods and Services Tax” and [Rank] > 2 then “Liability” else “”)
in
#“Added Custom1”

5

HI @AllisterB,

Can you please share sample file? Cheers!!

6

i have attached eth workbook.[Book100.xlsx|attachment]
Please note that I have used an Index Col which I think records the order in which the record sits in source data ??

My problemn is that in specifying that the first Instance is rank 2 - what say another record is added higher in the source table and teh record that was ranked 2 is now ranked 3.

(upload://vxisc8oRXTuRkdZE3cJpz12oWfZ.xlsx) (18.4 KB)

7

HI @AllisterB,

I am unable to open file. Can you please update sample file again?

8

Book200.xlsx (18.4 KB)

9

Hi @AllisterB,

This attachment does not match with the code you have provided. Can you provide sample code which matches with the header.

“Source.Name”, “BranchLine”, “ThisYear”, “LastYear”, “SheetName”, “SourceRowNumber”, “Notes”

10

The file I am working on is confidential and it would take some work to mock it up.
So I mocked up a indicative model
Trust it is OK.

11

Is it possible for you to explain this little bit more may be with dummy screen shot of present output with expected output.

12

I have attached a new file that shows the problem I was explaining.
I think the key is that the first instance of Salaries for a Branch is Income and the next one (and there are no more than two instances of Salaries for a Branch) is an expense.

Book300.xlsx (24.0 KB)

13

Hi @AllisterB

Thanks for your file. So you wanted to only consider the “Salary” data (dummy) and then categories based on rank as Income and expense. Below is the code you an use to do that.

Trick here is that you just need to separate the unwanted data with actual data and combine at the end.

let
    Source = Excel.CurrentWorkbook(){[Name="Table14"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Branch", type text}, {"Account", type text}, {"Value", Int64.Type}}),
    //split the rows between actual data and unnecessary data and then combine at the end so that you don't loose any data. If you have more than 1 condition then can use or to combine multiple data.
    FilteredRowsUnwanted = Table.SelectRows(#"Changed Type", each ([Account] <> "Salaries")),
    FilterRowsSalaries = Table.SelectRows(#"Changed Type", each ([Account] = "Salaries")),
    #"Added Index" = Table.AddIndexColumn(FilterRowsSalaries, "Index", 0, 1),
    #"Sorted Rows" = Table.Sort(#"Added Index",{{"Index", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Branch"}, {{"Count", each _, type table [Branch=text, Account=text, Value=number, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "rank",1,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Branch", "Account", "Value", "Index", "rank"}, {"Branch", "Account", "Value", "Index", "rank"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Income or Expense", each if [Account] = "Salaries" and [rank] = 1 then "Income" else
if [Account] = "Salaries" and [rank] > 1 then "Expense" else ""),

    //Combine the unnecessary data FilteredRowsUnwanted
    Custom1 = Table.Combine({#"Added Custom1",FilteredRowsUnwanted})
in
    Custom1

Hope below is the output you were looking for.

image
image695Ă—207 8.96 KB

Thanks
Mukesh

2 Likes
14

Thanks to all