question

Shinka-3536 avatar image
0 Votes"
Shinka-3536 asked Shinka-3536 commented

Reapeating row when merging tables

Hello everyone,

I'm procesing some financial data and have come across an issue I need help with.

Since financial information is usually disclosed as is, when importing the information everything comes along. However, I wish to calculate the subtotals in a pivot table.

What I normally do is create a supporting table with an "Account" column and a "Sub-Account" column. Then I merge both tables using Inner Join between the source file's "Account" column and the supporting table's "Sub-Account". That way only the sub-account concepts remain in the merged table, along with a new column with the "Accounts" column from the supporting table that will help me create the subtotals in a pivot table.

This time around, however, I'm having issues since the source file's "Accounts" column has an expresion that repeats for more than one "Account" or subtotal concept. So when I try the abovementioned way, I end up with bananas in the pivot table instead of the actual subtotals.

The expresion in question is "Ajuste por reexpresión" (sorry for the Spanish), and it appears five times under 5 different subtotals. I've tried all Join Kinds that power query offers to no avail. Each gave me a different result, but not the correct result.

At present, it's a 60 file query, and they'll keep coming, so I'm hoping to find an automate solution rather than changing the concepts by hand in each file each time...

I'm attaching a file where the expresions from the source file's column and their respective subtotals can be seen, and the supporting table I originally created.

File here: https://1drv.ms/x/s!AmrjlXSYqMxegZpBTwadY7uHeI50Aw?e=yLnNSl

Thanks in advance,

Shinka

power-query-not-supported
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Ehren avatar image
0 Votes"
Ehren answered Shinka-3536 commented

I took a look at your spreadsheet but didn't find any PQ queries in it. I'm not sure I entirely understand the scenario and what you're trying to do, but it sounds like you may need to remove duplicates or group the data before joining.

EDIT: After looking at the third version of the file, I think I now understand the issue. You need to derive both Account and Sub-Account from the initial table, and then join on both those columns. Here's one approach, which involves looking for ALL CAPS account names that are followed by a null account.

 let
     Origen = Excel.CurrentWorkbook(){[Name="Source"]}[Content],
     #"Tipo cambiado" = Table.TransformColumnTypes(Origen,{
                 {"Account", type text}, {"Amount", Currency.Type}}),
     #"Added Index" = Table.AddIndexColumn(#"Tipo cambiado", "Index", 0, 1, Int64.Type),
     #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if Text.Upper([Account]) = [Account] and #"Added Index"{[Index]+1}?[Account]? = null then [Account] else null, type nullable text),
     #"Filled Up" = Table.FillUp(#"Added Custom",{"Custom"}),
     #"Removed Columns" = Table.RemoveColumns(#"Filled Up",{"Index"}),
     #"Filas filtradas" = Table.SelectRows(#"Removed Columns", each [Amount] <> null and [Amount] <> ""),
     #"Renamed Columns" = Table.RenameColumns(#"Filas filtradas",{
                 {"Account", "Sub-Account"}, {"Custom", "Account"}}),
     #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Account", "Sub-Account", "Amount"}),
     #"Consultas combinadas" = Table.NestedJoin(#"Reordered Columns", {"Account", "Sub-Account"}, tbl_acc, {"Account", "Sub-Account"}, "tbl_acc", JoinKind.Inner),
     #"Removed Columns1" = Table.RemoveColumns(#"Consultas combinadas",{"tbl_acc"})
 in
     #"Removed Columns1"

Alternatively, you could skip the join altogether and just filter out the rows where the Account and Sub-Account are the same.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

It worked beautifully! Thank you SOOO much! You are awesome!

Sorry it took a while for me to explain the issue correctly, but you found an increadible solution nonetheless!

Thank you!!

0 Votes 0 ·
Shinka-3536 avatar image
0 Votes"
Shinka-3536 answered Ehren commented

Hi @Ehren

Sorry about that. I've updated the file.

Now there is 2 PQ conections and uploaded the tables to PV.

I hope the issue I'm having is easier to visualize now.

Thanks

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I took a look at the new file, but I still don't understand what the issue is. Can you include the table you would like to see in the output, or be more specific about what's wrong with the data coming from PQ?

0 Votes 0 ·

Updated the sample file.

First sheet shows as before with the resulting pivot table there as well.

Second sheet shows the expected result.

The difference lays in one acount name that is repeated 5 times, exactly alike. when this is corrected manually, the merge is done correctly and the resulting pivot table is a mirror of the original Source table, except the subtotals are calculated by the pivot table.

I'm looking for a way to perform the change of name to the account that is repeated into the type as demostrated in the example so that I can then merge 60 excel workbooks, to start. I'd like to do this in PQ so that future incoming information would be automatically processed and the names changed to the appropiate ones to create the correct pivot table that reflects the original data.

Thanks,

0 Votes 0 ·

Thanks. I think I understand the issue now. I've updated my answer above.

0 Votes 0 ·