Obtaining missing chunk and categorizing data

[vkarthik21]

Hi Team, I have two sets of data - one at Group level and one at Division Level. Not all divisional data is mapped out and I want to club the remaining chunk as ‘Others’.

To illustrate, here is the data that I have

Source Data.csv (566 Bytes)

What I am looking for is to merge the datasets based on Item and Date and club the remaining amount i.e. Group Level - Sum of Division Level as ‘Others’. Here is the expected result -

Reporting Date,Level,Item,Amount
31-Jan-21,Other,Assets,100
31-Jan-21,Other,Liabilities,-100
28-Feb-21,Other,Assets,100
28-Feb-21,Other,Liabilities,200

Essentially the group total should match the division total. In case the division total exceeds the difference i.e. the negative amount should be tagged as ‘Other’.

[amira.bedhiafi.pro]

Can you just explain more how did you get this ?

@vkarthik21

Reporting Date,Level,Item,Amount
31-Jan-21,Other,Assets,100

[vkarthik21]

Thanks @amira.bedhiafi.pro for the reply. Here is how the calculation is to be done.

1. For a given month (31-Jan-21) and Item (Assets), we first need to look at the ‘Group’ value in this case -
   31-Jan-21;Group;Assets;2000
2. For same month (31-Jan-21) and Item (Assets), we need to look for ‘Division’ Level value. In this case that will be -
   |31-Jan-21|Finance|Assets|500|
   |31-Jan-21|Corporate|Assets|1100|
   |31-Jan-21|Capital|Assets|300|
3. Sum up the above values i.e. 1900
4. Perform the difference of ‘Group’ level and ‘Division’ Level which would be 100 and tag it as ‘Others’.

Hope this explains. Please let me know if you need additional information

[Melissa]

Hi @vkarthik21,

Give this a go. Just paste the code in a new blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZHBCsMgDIbfxbOCsRR2HYMOxt6geEiHh0BRUff+u6ytNfWcn//Ll8yzGEC90CsD8pnCN8p7zq5kabTWwko+fxMutFIhlyWM/5C5qcktrGTQnXldYkZGmsij/7ithgceIcWQsOwRAJ7BSAXXapcOpV6Gpw7UyZyfZ+Nd1R3+jdrFBZkbL2nUdnmOOR2af6Mr1wE2dcLaHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Reporting Date,Level,Item,Amount" = _t]),
    DemoteHeaders = Table.DemoteHeaders(Source),
    SplitColumn = Table.SplitColumn(DemoteHeaders, "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),
    PromoteHeaders = Table.PromoteHeaders(SplitColumn, [PromoteAllScalars=true]),
    ChType = Table.TransformColumnTypes(PromoteHeaders,{{"Reporting Date", type date}, {"Level", type text}, {"Item", type text}, {"Amount", Int64.Type}}),
    GroupData = Table.Buffer( Table.RemoveColumns( Table.SelectRows( ChType, each [Level] = "Group"), "Level" )),
    DivisionData = Table.Group(Table.SelectRows(ChType, each ([Level] <> "Group")), {"Reporting Date", "Item"}, {{"Temp Amount", each List.Sum([Amount]), type nullable number}}),
    NewAmount = Table.RemoveColumns( Table.AddColumn(DivisionData, "Amount", each Table.SelectRows( GroupData, (IT) => IT[Reporting Date] = [Reporting Date] and IT[Item] = [Item]){0}[Amount]? - [Temp Amount], type number ), "Temp Amount" ),
    NewLevel = Table.ReorderColumns( Table.AddColumn(NewAmount, "Level", each "Other", type text), Table.ColumnNames( ChType )),
    CombinedResult = Table.Combine({ChType, NewLevel})
in
    CombinedResult 

I hope this is helpful.

[EnterpriseDNA]

Hi @vkarthik21, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

[vkarthik21]

Thanks @Melissa for the reply. Could you please let me know where in you have specified the source file path? I haven’t been able to incorporate the language into the I am not across M language.

[Melissa]

Hi @vkarthik21,

I haven’t actually… it wasn’t a large sample so I just copied the data into a table which get’s translated to binary by Power Query Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(.

Just to clarify - a variable is the name you see before the equal sign and it will depend:

1. If your “Source” table has the column names in the Header, enter your variable name here OR
2. If it doesn’t enter your variable name here
3. and remove the steps above it from the sample provided by me

I hope this is hepful.

[vkarthik21]

Thanks @Melissa. It works perfectly.