add columns from data contained in 1 column (based on indentation)

[Daurenbrun]

Dear Community,

Need help to split one column data into several based on indentation in that first column.

The thing is I can do it in Excel by macro (indentlevel), however when uploaded into Power Query the indentation dissapears.

 Intention is to just create plain table to make further analysis.

Account
Currency
Client	column to add	column to add	column to add	column to add
Document	Account	Currency	Client	Document
1210	1210
EUR	1210	EUR
XXXXXX	1210	EUR	XXXXXX
B1	1210	EUR	XXXXXX	B1
B2	1210	EUR	XXXXXX	B2
B3	1210	EUR	XXXXXX	B3
B4	1210	EUR	XXXXXX	B4

[v-stephen-msft · Accepted solution]

Hi @Daurenbrun ,

Here's my approach, and I'm trying to get as close to your expected outcome as possible.

Load your data into Power Query:

Add a Custom Column to Identify Indentation Levels:

Go to the “Add Column” tab and select “Custom Column”.
Use a formula to identify the indentation level. For example, if indentation is represented by spaces, you can count the number of leading spaces:
= Text.Length([Account]) - Text.Length(Text.TrimStart([Account]))
Create a New Column for Each Indentation Level:

Add a custom column for each level of indentation. For example, for level 1:
Repeat this for each level of indentation, adjusting the condition accordingly.
Fill Down the Values:

Select the new columns created for each indentation level.
Go to the “Transform” tab and select “Fill” -> “Down”.
Remove Unnecessary Columns:

Remove the original column and the “IndentationLevel” column if they are no longer needed.
Here's final result：

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tKkrNS65UitUBcnIyU/NKwEyX/OTSXBjH0MjQAMxQAALX0CA4GwQiwABFCAKcDLEJGmETNMYmaKIUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Account = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Length([Account]) - Text.Length(Text.TrimStart([Account]))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom] = 0 then [Account] else null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each if [Custom] = 4 then [Account] else null),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each if [Custom] = 8 then [Account] else null),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Custom.4", each if [Custom] = 14 then [Account] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom4",{"Custom.2", "Custom.3", "Custom.4", "Custom.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Custom"})
in
    #"Removed Columns"

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Daurenbrun]

Hi, Stephen

Thank you

The problem is when I get original file from Excel to Query indentation dissappears, so we cannot use Text.Trimstart function. In original Excel file it is like in column "Account". But when it opened in Query the first letter in each raw starts from zero.

[lbendlin]

Can you post a sample (sanitized) raw file from "1C accounting program" ?

[Daurenbrun]

Account

Currency

Client

Document

1210 (Account)

EUR

XXXXXX Client1

B1 Doc1

B2 Doc2

B3 Doc 3

B4 Doc 4

[lbendlin]

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

[lbendlin]

Hopefully this is not how your actual source data looks like?  Where is it coming from?

[Daurenbrun]

No, my actual data loos like as first column - name is currency. The rest 4 column should be result

This is how 1C accouunting program, so not convinient.

By the way, I solved this by splitting the first column, so there left only 3-4 similar texts in the column.

Then I've used if else function to add new column (using above texts) and did "fill down".

It took more steps, but in Excel we can use a macros with indent.level function.