Transform Table Structure

**jrean042** · 06-20-2023, 09:41 AM

I have an export out of our ERP system of cost centers.
I am trying to transform this from a tabular structure to a horizontal structure based on the number of levels the node has.

I have a mock-up attached. In this file, I have used the TRANSPOSE(UNIQUE) formula to get the number of levels in the node and an IF formula to transform the structure, this is highlighted in yellow. I'm sure this can be done in PQ, which I am decent at, but I need help completing this task.

Any help or guidance would be appreciated.

**Fluff13** · 06-20-2023, 09:53 AM

How about

Formula:

Please Login or Register  to view this content.

**jrean042** · 06-20-2023, 09:56 AM

That is VERY useful but would like to do it in PQ so I can do additional transformation steps and join it with other tables in a model.

**Fluff13** · 06-20-2023, 10:00 AM

In that case you will need to wait for somebody else, as I don't know PQ

**AliGW** · 06-20-2023, 10:05 AM

I'd prepared this (doesn't need the level column):

Please Login or Register  to view this content.

**jrean042** · 06-20-2023, 10:27 AM

@AliGW This also works, but looking for a PQ solution

**AliGW** · 06-20-2023, 10:29 AM

Yes, I realise that now.

**AliGW** · 06-20-2023, 10:38 AM

There's probably a more efficient way.

M Code:

Please Login or Register  to view this content.

**jrean042** · 06-20-2023, 11:17 AM

@AliGW, This works, is there a way to fill down based on a condition?
I want to fill down the text, but not the cost center numbers.

**AliGW** · 06-20-2023, 11:30 AM

You've lost me.

What do you want to fill down, where and why?

**jrean042** · 06-20-2023, 12:01 PM

Sorry, I have attached a sample of what I am trying to do.
I highlighted the end result in blue.

**Fluff13** · 06-20-2023, 12:06 PM

I know you want to use PQ, but for the fun of it, another formula that just looks at col A

Formula:

Please Login or Register  to view this content.

Haven't sussed the fill down yet

**AliGW** · 06-20-2023, 12:07 PM

I wish you'd said that before wes started!

I am not the best at PQ, and there will be others better than I, but if I were doing it, I'd split each column conditionally (based on text or number), then fill down the text, then rejoin conditionally, but it would have to be done on each column separately.

It is early evening here and I'm about to log off, but I'll throw up a call for help before I do.

**jrean042** · 06-20-2023, 01:25 PM

My apologies, I assumed (incorrectly) that I could fill down and get my desired outcome. It was only after I had a working PQ option that I realized that this would not work.

Thank you for all the time you put into this question.

**Fluff13** · 06-22-2023, 07:58 AM

With the lack of a PQ solution, here is a formula that will do what you want, if interested.

Formula:

Please Login or Register  to view this content.

**jrean042** · 06-22-2023, 04:21 PM

Originally Posted by Fluff13

With the lack of a PQ solution, here is a formula that will do what you want, if interested.

Formula:

Please Login or Register  to view this content.

This solution accomplishes what I am trying to do. How would you even go about writing a formula such as this? It works!!
Can it be altered to pull the cost center number into the last column? I have added this formula after I pasted the above formula:

Please Login or Register  to view this content.

**JEC.** · 06-22-2023, 05:07 PM

I only used column A as data source. Output is on the first sheet

Please Login or Register  to view this content.

**Fluff13** · 06-23-2023, 07:09 AM

How about

Formula:

Please Login or Register  to view this content.

**jrean042** · 06-23-2023, 12:13 PM

Originally Posted by Fluff13

How about

Formula:

Please Login or Register  to view this content.

Excellent!! This solves my question, thank you so much for your help with this!!

**Fluff13** · 06-23-2023, 01:05 PM

Glad to help & thanks for the feedback.

**Bo_Ry** · 06-23-2023, 01:31 PM

Please try

Formula
=LET(z,TEXTBEFORE(A2:A345," (",,,1),c,SEQUENCE(,6),d,VSTACK("|- ","< ",UNICHAR(12288)),y,IFNA(TEXTSPLIT(TEXTAFTER(z,d,c),d),"|"),
SUBSTITUTE(TRANSPOSE(SCAN("",TRANSPOSE(y),LAMBDA(a,v,IF(v="",a,v)))),"|",))

Power Query

PHP Code:


let 
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], 
    Clean = Table.TransformColumns(Source, {"Cost Center Hierarchy", each Text.Combine(Text.SplitAny(Text.BeforeDelimiter(_," ("),"|- <")) }), 
    Split = Table.SplitColumn(Clean, "Cost Center Hierarchy", Splitter.SplitTextByDelimiter("　"), {"1".."6"}), 
    Fill = List.Accumulate({{null,"|"},{"",null},{0},{"|",null}},Split, 
        (s,l)=> if l{0}=0 then Table.FillDown(s,{"1".."6"}) else Table.ReplaceValue(s,l{0},l{1},Replacer.ReplaceValue,{"1".."6"})) 
in 
    Fill

Transform Table Structure

LinkBack

Thread Tools

Rate This Thread

Display

Transform Table Structure

Re: Transform Table Structure

Re: Transform Table Structure

Re: Transform Table Structure

Re: Transform Table Structure

Re: Transform Table Structure

Re: Transform Table Structure

Re: Transform Table Structure

Re: Transform Table Structure

Re: Transform Table Structure

Re: Transform Table Structure

Re: Transform Table Structure

Re: Transform Table Structure

Re: Transform Table Structure

Re: Transform Table Structure

Re: Transform Table Structure

Re: Transform Table Structure

Re: Transform Table Structure

Re: Transform Table Structure

Re: Transform Table Structure

Re: Transform Table Structure

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How to structure my table for both stacked area chart and pivot table?

Re: Extract subarray from table column based on criteria matched-structure table reference

[SOLVED] Change data structure using Formula or Power Query ( Get & Transform)

[SOLVED] Transform table to database structure

[SOLVED] How do I transform my table?

transform table vba

Transform table

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions