Transpose with Multiple Columns and Rows

[qinh_ann]

Hello,

I have a table as below that needs tranformation in power query. This tabels have multiple fields spread across multiple rows and columns for a certain customers that need to be in a separate columns. The tabe below only shows the genral format of the original one, which has mre than just 4 customers. All of the values for each fields has been denoted as x for discretion purpose, and any cell that is blank represent null:

Customer Number	Customer Name	Column1.2	Column1.3	Column1.4	Column1.5
000001	Abc	FAX NO  : xxxx	CREDIT   : x	TYPE     : x	SALESMAN      : xxxxx
000001	Abc	POSTCODE: xx	TERMS    : xx	GROUP    : xx	AREA/TERR/REG : x/x/x
000001	Abc	CONTACT : xxxx	LIMIT    : xxxxx	PRICECODE: x	INDUSTRY      : x
000001	Abc		SALES TAX:	DISCOUNT : xxx	BRANCH        : xxx
000001	Abc		AGE METHD: x	AGE CYCLE: x	STATEMENT     : x
000001	Abc		PHONE NO : xxxxxx		REMINDER      : x
000002	Bcd	FAX NO  : xxxx	CREDIT   : x	TYPE     : x	SALESMAN      : xxxxx
000002	Bcd	POSTCODE: xx	TERMS    : xx	GROUP    : xx	AREA/TERR/REG : x/x/x
000002	Bcd	CONTACT : xxxx	LIMIT    : xxxxx	PRICECODE: x	INDUSTRY      : x
000002	Bcd		SALES TAX:	DISCOUNT : xxx	BRANCH        : xxx
000002	Bcd		AGE METHD: x	AGE CYCLE: x	STATEMENT     : x
000002	Bcd		PHONE NO : xxxxxx		REMINDER      : x
000003	Cde	FAX NO  : xxxx	CREDIT   : x	TYPE     : x	SALESMAN      : xxxxx
000003	Cde	POSTCODE: xx	TERMS    : xx	GROUP    : xx	AREA/TERR/REG : x/x/x
000003	Cde	CONTACT : xxxx	LIMIT    : xxxxx	PRICECODE: x	INDUSTRY      : x
000003	Cde		SALES TAX:	DISCOUNT : xxx	BRANCH        : xxx
000003	Cde		AGE METHD: x	AGE CYCLE: x	STATEMENT     : x
000003	Cde		PHONE NO : xxxxxx		REMINDER      : x
000004	Def	FAX NO  : xxxx	CREDIT   : x	TYPE     : x	SALESMAN      : xxxxx
000004	Def	POSTCODE: xx	TERMS    : xx	GROUP    : xx	AREA/TERR/REG : x/x/x
000004	Def	CONTACT : xxxx	LIMIT    : xxxxx	PRICECODE: x	INDUSTRY      : x
000004	Def		SALES TAX:	DISCOUNT : xxx	BRANCH        : xxx
000004	Def		AGE METHD: x	AGE CYCLE: x	STATEMENT     : x
000004	Def		PHONE NO : xxxxxx		REMINDER      : x

I need to get that table transformed into this format below:

Customer Name

Customer Number

Fax No.

Postcode

Contact

Credit

Terms

Limit

Sales Tax

Age Mthd

Phone No.

Type

Group

Pricecode

Discount

Age Cycle

Salesman

AREA/TERR/REG

Industry

Branch

Statement

Reminder

00001

Abc

x

x

x

x

x

x

x

x

x

x

x

x

x

x

x

x/x/x

x

x

x

x

00002

Bcd

x

x

x

x

x

x

x

x

x

x

x

x

x

x

x

x/x/x

x

x

x

x

00003

Cde

x

x

x

x

x

x

x

x

x

x

x

x

x

x

x

x/x/x

x

x

x

x

00004

Def

x

x

x

x

x

x

x

x

x

x

x

x

x

x

x

x/x/x

x

x

x

x

I figured that this might just need some work around with split by delimeter, group by, transpose and maybe some pivot/unpivot as well but I'm not sure what's the quickest and smartest way to do so while handling all the nulls in the table as well.

Any help on this would be much appreciated, thanks in advance!!

[wdx223_Daniel]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZHbaoQwEIZfZfB6wZ6u9m4apyqYRJIIK8ve7KHPsI/fiVWzoZZSTQLCDMmXf/yOx+zJr+dsl+H5wt8PPIDSAHu48+KGMFTUDoYOl65vCWAuLTZkJSqYesOt0+4Ht9XWCV2QP+IxZKSdrnBdGt21DzUawpwPmdxQ6Zs57yWu0MqhcCFvU8sh7hyG3za1oPFxLmtVdNaZfs68xIVpOHB42HNR1FboTo0vcePdoBIVwMPov4GwJJDkquI7gC9FL5oxj3XoSJJyf+VpK63I2xlHu499Q5JnIrMw0IsPerkmFxu4acUGblqxgbtRbAxaLTbGrBD76n/R9ZZcbOCmFRu4acUG7kaxMWi12BizQuybT377TC42cNOKDdy0YgN3o9gYtFpsjPmH2NMX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer Number" = _t, #"Customer Name" = _t, Column1.2 = _t, Column1.3 = _t, Column1.4 = _t, Column1.5 = _t]),
    Custom1 = Table.Combine(Table.Group(Source,{"Customer Number","Customer Name"},{"n",each Table.PromoteHeaders(Table.Transpose(Table.FromRows({{"Customer Number",[Customer Number]{0}},{"Customer Name",[Customer Name]{0}}}&List.TransformMany(List.Skip(Table.ToColumns(_),2),each List.Transform(List.RemoveItems(_,{" "}),each Text.Split(Text.Trim(_),":")),(x,y)=>y))))})[n])
in
    Custom1

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZHbaoQwEIZfZfB6wZ6u9m4apyqYRJIIK8ve7KHPsI/fiVWzoZZSTQLCDMmXf/yOx+zJr+dsl+H5wt8PPIDSAHu48+KGMFTUDoYOl65vCWAuLTZkJSqYesOt0+4Ht9XWCV2QP+IxZKSdrnBdGt21DzUawpwPmdxQ6Zs57yWu0MqhcCFvU8sh7hyG3za1oPFxLmtVdNaZfs68xIVpOHB42HNR1FboTo0vcePdoBIVwMPov4GwJJDkquI7gC9FL5oxj3XoSJJyf+VpK63I2xlHu499Q5JnIrMw0IsPerkmFxu4acUGblqxgbtRbAxaLTbGrBD76n/R9ZZcbOCmFRu4acUG7kaxMWi12BizQuybT377TC42cNOKDdy0YgN3o9gYtFpsjPmH2NMX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer Number" = _t, #"Customer Name" = _t, Column1.2 = _t, Column1.3 = _t, Column1.4 = _t, Column1.5 = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Customer Number", "Customer Name"}, "Attribute", "Value"),
    #"Removed Other Columns" = Table.SelectColumns(#"Unpivoted Other Columns",{"Customer Number", "Customer Name", "Value"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Other Columns", "Value", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"Column", "Value"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Customer Number", Int64.Type}, {"Customer Name", type text}, {"Column", type text}, {"Value", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Column", Text.Trim, type text},{"Value", Text.Trim, type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Trimmed Text", each ([Value] <> null and [Value] <> "")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Column]), "Column", "Value")
in
    #"Pivoted Column"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

[Shravan133]

To transform the original table into the desired format using Power Query in Excel, you can follow these steps:

1. Load the original table into Power Query.
2. Unpivot the columns except for "Customer Number" and "Customer Name" columns.
3. Split the "Attribute" column to separate field names and values.
4. Pivot the field names column.
5. Merge the pivoted data with the "Customer Number" and "Customer Name" columns.
6. Clean up the table and handle null values.

Here's a step-by-step guide:

1. **Load the original table into Power Query**:
- Select the range of cells containing your data.
- Go to the "Data" tab, then click "From Table/Range" to load the data into Power Query.

2. **Unpivot the columns**:
- Select all columns except "Customer Number" and "Customer Name".
- Go to the "Transform" tab, then click "Unpivot Columns" > "Unpivot Other Columns".

3. **Split the "Attribute" column**:
- Right-click on the "Attribute" column, then click "Split Column" > "By Delimiter".
- Choose the delimiter ":", then split into "At the Right-most delimiter".
- This will split the "Attribute" column into two columns: one for field names and one for values.

4. **Pivot the field names column**:
- Select the field names column (e.g., "Column1.2").
- Go to the "Transform" tab, then click "Pivot Column".
- Choose the values column (e.g., "Value") for "Values Column", and select "Don't Aggregate" for "Aggregate Value Function".

5. **Merge pivoted data with "Customer Number" and "Customer Name" columns**:
- Select the "Customer Number" and "Customer Name" columns along with the pivoted data.
- Go to the "Home" tab, then click "Merge Queries" > "Merge Queries as New".

6. **Clean up the table and handle null values**:
- Replace null values with "x" or any other desired value.
- Rename columns as needed.
- Remove unnecessary columns.

After completing these steps, you should have a transformed table with each customer's data in separate columns. Make sure to adjust the steps according to your specific column names and data structure.