Power Query - Data Cleaning Question

[bbalch]

I'm trying to use Power Query to clean up some data and I've hit a road block. My knowledge in power query is very limited so I'm hoping someone with more experience can help me out.

I've attached a link with screenshots of the data model (original.png) and the desired outcome (desired.png)

I have an "account" column in the data model that includes account numbers / names and splits. Splits are line items broken out of that account, for example the Auto Expense account may have a split for maintenance and another for other. The account numbers are in this format [509050_Auto_Expense] Auto Expense while splits are in this format " Other (00-100 General and Admin)". As seen in original.png

I would like to add a "splits" column that would only pull over the splits from the "account" column and leave it blank if the "accounts" column contains an account number. Then in the "account" column I would like to replace the splits that were moved into the split column with the account number from above. See desired.png as an example.

Original:

Accounts
[509050_Auto Expense] Auto Expense
Other (00-100 General and Admin)

Desired:
Accounts |Splits
[509050_Auto Expense] Auto Expense |
[509050_Auto Expense] Auto Expense | Other (00-100 General and Admin)


https://cmaworld.box.com/s/zbmjey1eqs6k7r8fs2alo548yfp2fmbp

Any suggestions or guidance are greatly appreciated!

 

[horseyride]

I can't see your sample image, but assuming source is a single column table named Table1 that looks like this:

[TABLE="width: 245"]
<tbody>[TR]
[TD]Accounts[/TD]
[/TR]
[TR]
[TD][509050_Auto Expense] Auto Expense[/TD]
[/TR]
[TR]
[TD]Other (00-100 General and Admin)[/TD]
[/TR]
[TR]
[TD]Pepsi (Free)[/TD]
[/TR]
[TR]
[TD][509020_Auto Expense] Auto Expense[/TD]
[/TR]
[TR]
[TD][509030_Auto Expense] Auto Expense[/TD]
[/TR]
[TR]
[TD]Pepsi (Free)[/TD]
[/TR]
[TR]
[TD]Pepsi (Lite)[/TD]
[/TR]
[TR]
[TD]Pepsi (Diet)[/TD]
[/TR]
</tbody>[/TABLE]

then this should work

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Replaced Value" = Table.ReplaceValue(Source,"[","[[",Replacer.ReplaceText,{"Accounts"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "Accounts", Splitter.SplitTextByEachDelimiter({"["}, QuoteStyle.Csv, false), {"Accounts.1", "Accounts.2"}),
    #"Filled Down" = Table.FillDown(#"Split Column by Delimiter",{"Accounts.2"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Filled Down",{"Accounts.2", "Accounts.1"})
in
    #"Reordered Columns"

It replaces [ with [[ then splits on the first [ then fills down to place the code in blank rows