Power Query Merge for range of account numbers

[cmncp]

I am doing some Financial / GL Reporting, and need to expand a From/To range of GL Accounts into a list of account numbers.

The following image shows the requirement:

- I have a Financial Statement table which contains From / To account groupings

- I also have a GL Account table which lists all GL accounts

- I need to merge these 2 to generate the "New Merged Table" on the right

For example, if you look at the Assets type, it contains all accounts between 00010000 and 00019999.  So when this gets merged with the GL account table, it needs to populate with the 3 GL accounts that exist within this range.

I assume I will need to write some custom "M" code to do this, but I can't figure out the code.  Any help is greatly appreciated.

[amitchandak]

@cmncp , in Dax you have crossjoin .

Try like

filter(crossjoin([financial statement],[Account]), [Account] <=[to Account] && [Account]>=[from Account])

Cross join do no take same column name from two tables, so use selectcolumns to rename if needed

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

M - https://radacad.com/append-vs-merge-in-power-bi-and-power-query

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.

[cmncp]

Financial Statement Table

Statement Name	Type	From Account	To Account
All Accounts	Assets	00010000	00019999
All Accounts	Liabilities	00020000	00029999
All Accounts	Expenses	00030000	00039999
All Accounts	Income	00040000	00049999
P&L	Expenses	00030000	00039999
P&L	Income	00040000	00049999

Accounts Table

Account	Description
00010000	Asset A
00010001	Asset B
00010003	Asset C
00020000	Liability A
00020001	Liability B
00020003	Liability C
00020000	Expense A
00020001	Expense B
00020003	Expense C
00020000	IncomeA
00020001	IncomeB
00020003	IncomeC

New Merged Table

Statement Name	Type	Account	Description
All Accounts	Assets	00010000	Asset A
All Accounts	Assets	00010001	Asset B
All Accounts	Assets	00010003	Asset C
All Accounts	Liabilities	00020000	Liability A
All Accounts	Liabilities	00020001	Liability B
All Accounts	Liabilities	00020003	Liability C
All Accounts	Expenses	00020000	Expense A
All Accounts	Expenses	00020001	Expense B
All Accounts	Expenses	00020003	Expense C
All Accounts	Income	00020000	IncomeA
All Accounts	Income	00020001	IncomeB
All Accounts	Income	00020003	IncomeC
P&L	Expenses	00020000	Expense A
P&L	Expenses	00020001	Expense B
P&L	Expenses	00020003	Expense C
P&L	Income	00020000	IncomeA
P&L	Income	00020001	IncomeB
P&L	Income	00020003	IncomeC

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[JennyCen]

Hi Ashish,

I have the same problem. Is the PBI file still available? I would be very happy!

Regards

Jenny

[Ashish_Mathur]

Hi,

That file is not available.  Start a new thread, explain the question and show the expected result.

[cmncp]

Exactly what I was trying to do! Thanks

[Ashish_Mathur]

You are welcome.