Power Query - filter or split data import

[Anonymous]

Hi Forum

I havent managed to find a solution for this, i'm having the same 'structural issue' from multiple data exports from my systems. 

The data exported, includes a grouping as a header in the same column as the data. In my example the data exported is general ledger accounts. The column headings for each account is simple, date,description and amount. However, the account name appears in the same column as the data, so any filter on the data does not include the account name. A sample below:

Date	Description	Amount
advertising
2022/01/01	aaa	1
2022/01/01	bbb	2
suscriptions
2022/01/01	ccc	5
2022/01/01	ddd	9
2022/01/01	eee	12
2022/01/01	ffff	11
maintenance
2022/01/01	ggg	14
2022/01/01	hhh	13
2022/01/01	iiii	29

In order to do effective queries on the grouping, or label, i would ideally like the data to look this, adding the account name to each row.

Account	Date	Description	Amount
advertising	2022/01/01	aaa	1
advertising	2022/01/01	bbb	2
suscriptions	2022/01/01	ccc	5
suscriptions	2022/01/01	ddd	9
suscriptions	2022/01/01	eee	12
suscriptions	2022/01/01	ffff	11
maintenance	2022/01/01	ggg	14
maintenance	2022/01/01	hhh	13
maintenance	2022/01/01	iiii	29

Perhaps there is even an advanced data object i can use to place the values into. Any help would really be appreciated.

[BA_Pete · Accepted solution]

Hi @Anonymous ,

Paste this code into a new blank query in PQ to see the steps I took:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZAxDsIwDEWvUmWuRBNg4CxRB8cxiQcMagLnx+mGTL/sP/jJ1pdjdJA/tHVuLMXNbtp7naMLSwinxWvpCADUvQUpJfWwg+lnv70bbvzq/JR2fBgR1a8W5JzVbxYQ0YgSLLmrBvJ/0jyApZOAIB2HKWW8wF8sqbUOcraEVeMDmnT9Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Description = _t, Amount = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Description", type text}, {"Amount", Int64.Type}}),
    addAccount = Table.AddColumn(chgTypes, "Account", each if [Amount] = null then [Date] else null),
    fillDownAccount = Table.FillDown(addAccount,{"Account"}),
    filterNullAmount = Table.SelectRows(fillDownAccount, each ([Amount] <> null))
in
    filterNullAmount

SUMMARY:

1) Add column that picks up the account name where amount is null.

2) Fill down this new [Account] column.

3) Filter out null [Amount] values to only leave rows that have useful data

I get this output:

Pete