Transform a table of 12 columns into 3

pedroccamara · December 10, 2020, 5:37am

Hello everyone,
I have this table wich i have merged to another and got this result: a table of 30K rows with 12 columns.
I would like to have only 3. I know i can do it with new queries and then i would append them all but i believe it will be slower and also, would like to know if there’s another way of doing it.
I’m attaching a sample.

append or.xlsx (18.0 KB)

The main table is what i have. The others 6 on the right, is what i can get from the main table and append them all after. I wonder if it’s possible to do it any other way?
Thank you all
Pedro

Melissa · December 10, 2020, 7:30am

Hi @pedroccamara,

From what you have written:

You have a merged table with 12 columns
You would like 3 columns
The 6 on the right, is what you can get from the main table (There are no 6 tables on the right)
Append them all after (is this the desired result 6x 3 columns appended into 1 fact table)

What did the source data look like before the Merge?
Be sure to provide a mock-up of the desired end result, to illustrate your requirement.

Thanks!

pedroccamara · December 10, 2020, 11:18am

Hello @Melissa
I’m so glad that you’ve replied to me on this one. I believe this one, is like a “follow up” on my last post that you’ve answer and all because that you “gave me” this idea.
The purpose is to get faster results because today is 30k lines but tomorrow a lot more.

The source data, to answer your question, has a date field, account id, credits, debits and a bunch of a lot of description columns.

I’ve attach a pbi file and an excel file with the MainTable.
As you can see in query editor, this is what i did but i believe it can be done faster and more secure way because today is 9 levels of accounts but tomorrow it can be 13. Am i right?
Let me know if i forgot anything
Thanks a lot Melissa
append or.xlsx (23.3 KB) Transform a table of 12 into 3.pbix (53.7 KB)

Melissa · December 10, 2020, 12:30pm

Hi @pedroccamara,

Thanks for the additional details.
See how this query works for you.

let
    Source = MainTable,
    ListLevels = List.Skip( Table.ColumnNames( Source ), 3 ),
    CreateLevels = Table.AddColumn( Source, "Accounts", each List.RemoveNulls( Record.ToList(Record.SelectFields(_, ListLevels)))),
    SelectColumns = Table.SelectColumns(CreateLevels, List.Difference( Table.ColumnNames(CreateLevels), List.Combine({{"Account"}, ListLevels}) )),
    ExpandToRows = Table.ExpandListColumn(SelectColumns, "Accounts"),
    GroupRows = Table.Group(ExpandToRows, {"Accounts"}, {{"Debits", each List.Sum([Debits]), type nullable number}, {"Credits", each List.Sum([Credits]), type nullable number}}),
    AddLiquid = Table.AddColumn(GroupRows, "Liquid", each [Debits] - [Credits], type number)
in
    AddLiquid

.
This is what each of the steps do:

Created a list for all levels by excluding the first 3 columns