Latest Enterprise DNA Initiatives

Transform a table of 12 columns into 3

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

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!

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)

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
image

Stepped back to the MainTable and created a list with all accounts from that record

Dynamically selected the columns

Expanded the lists to new rows
image

Group by Accounts

and finally added Liquid

I hope this is helpful

2 Likes

Hello @Melissa
Such an amazing solution Melissa. Awesome!!!

Thank you so so much