cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sifar786 Member
Member

Merge or concatenate values in Top 2 Rows into a single Row to Promote as Headers

I have a table data imported from csv's or xlsx file that looks like this:

Column1

Column2

Column3

Column4

Column5

Column6

Column7

Column8

Column9

Column10

null

null

null

null

null

null

null

Units

Units

%Reach

Mkts

Dept

SCat

Cat

Seg

Brand

Upc

4 W/E 10/06/17

4 W/E 11/03/17

4 W/E 12/01/17

ABC

someDept1

someCat1

FOOD

VEGGIES

XWAR

3939493

2321.11

6883.43

49.13

ABC

someDept1

someCat1

FOOD

VEGGIES

XWAR

5946942

422.32

222.64

91.84

ABC

someDept1

someCat1

FOOD

VEGGIES

XWAR

4938843

2543.34

null

null

CDE

someDept2

someCat2

BEV

NON-VEG

SAG

0549403

null

null

2

DEF

someDept3

someCat3

UTIL

DAIRY

MUG

04032850

2

null

null

 

The Columns 1 to 7 indicate Dimension columns (see Row 2). This may be variable between different datasets and one may not know their names. Similarly, the Columns 8 to 10 indicate Fact columns (see Row 1). This may also be variable between different datasets and one may not know their names. The only way is that there are "null" values in the 1st row till the start of the Fact Columns.

 

I want to merge the values in these top 2 rows into a single row using "~" as a Delimiter, the single row, which i can then promote as Header. The final table should look like this:

Mkts

Dept

SCat

Cat

Seg

Brand

Upc

Units~4 W/E 10/06/17

Units~4 W/E 11/03/17

%Reach~4 W/E 12/01/17

ABC

someDept1

someCat1

FOOD

VEGGIES

XWAR

39393493

2321.11

6883.43

49.13

ABC

someDept1

someCat1

FOOD

VEGGIES

XWAR

59493942

422.32

222.64

91.84

ABC

someDept1

someCat1

FOOD

VEGGIES

XWAR

49382843

2543.34

null

null

CDE

someDept2

someCat2

BEV

NON-VEG

SAG

05490403

null

null

2

DEF

someDept3

someCat3

UTIL

DAIRY

MUG

04032850

2

null

null

 

Note:

  1. I am using "~" as a Delimiter for the next part of the query where i will be pivoting the Facts columns, splitting and removing the dates. 
  2. I cannot TRANSPOSE Rows to Columns as the number of rows exceed 16,384 and there is a limitation on TRANSPOSE in Power Query that it can only handle 16,384 columns.
1 ACCEPTED SOLUTION

Accepted Solutions
sifar786 Member
Member

Re: Merge or concatenate values in Top 2 Rows into a single Row to Promote as Headers

Hi @v-frfei-msft@Nathaniel_C ,

Thank you all for your valuable inputs. 🙂

Last night after posting, i gave a last try and came up with this solution. Though it is long, it does serve my purpose.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZLdSsNAEIVfpQS8q5vdmUncvczPtgRsI41plZCLoEFFrGL7/jgTi0nBq0I453DIfrNk0jRBMP/nqfdvx8PErzZ99/QatPMmWL0PTd5/HdmqrBP71ap/YU2/u/2znL3LWGm2C/3M6FDHobkZCxNqPCsg1EYKmZGkcvTw+dHLGHPKPETioixztq1fLgtfcXrYJRs2dOjIISdAMMrIu7G1qEg6csrg5fDIUewIBASgUAJwiImDM8rS5Wi+s7XDHSEiVEinLQgwy/0ECCNQYuq3rOtyfc1M+fyJqI7Ikca/VcIAyv1iAsIRJLG+L25lpUmxeWRf1QOHIWAjPf4WbfsD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t]),
    
    // take first 2 rows and transform them
    First2Rows = Table.FirstN(Source, 2),
    TransposeRows2Columns = Table.Transpose(First2Rows),
    AddCustomColumn = Table.AddColumn(TransposeRows2Columns, "Custom", each if [Column1] = "" then [Column2] else [Column1]&"~"&[Column2]),
    RemoveFirst2Columns = Table.RemoveColumns(AddCustomColumn,{"Column1", "Column2"}),
    TransposeColumn2Rows = Table.Transpose(RemoveFirst2Columns),
    SourceWithout2FirstRows = Table.RemoveFirstN(Source, 2),
    CombineRows2Source = Table.Combine({TransposeColumn2Rows,SourceWithout2FirstRows}),
    PromotedFirstRowAsHeaders = Table.PromoteHeaders(CombineRows2Source, [PromoteAllScalars=true])
in
    PromotedFirstRowAsHeaders

The Result is how i expect it:PowerBI test table.JPG

 

Note: I am only transposing the 1st 2 rows as the dataset is huge and it will exceed 16384 columns if i transpose all. Besides, it is also going to hog my memory.

 

A few observations of PowerBI:

  1. Can the steps be shortened or is there a more efficient way of doing this? I have more steps following these steps to do further transformations.
  2. Right now, i am considering only 1st 2 rows. But what if one of the data extracts has more number of null rows on top and some cells may contain text in it? I think the code would fail to determine the Dimensions and Facts columns correctly.
  3. I cannot auto-resize the columns though it does show the resize arrows.
  4. I am unable to enter null values in cells in Power BI > Enter Data.

Can someone address these questions inorder to have a concise, dynamic and efficient solution? I am here to learn!

View solution in original post

3 REPLIES 3
Super User IV
Super User IV

Re: Merge or concatenate values in Top 2 Rows into a single Row to Promote as Headers

Hi @sifar786 ,

 

Using Group by this might work.

 

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Community Support Team
Community Support Team

Re: Merge or concatenate values in Top 2 Rows into a single Row to Promote as Headers

Hi @sifar786 ,

 

To remove top 1 row and Promoted Headers. M code for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZFPS8NAEMW/Sgl4q5vdnUncPebPtgRsA4lpLaGH0AYVayy2fn9nopBYT/by3uNt8puBqWuv+zwcvOk/rOpezqeR3xRts3v2ttPaW7z2Tdoez2Rl0rB9a9k+kcYfTbfnf487UpysfTdR0pehr+6GQvkSfhXal4oLnhHFCT2c3t9aHqN+Mg3hOMvzlGzl5vPMlZQe11FBBhYsWqCkQSuh+NvQGBDIHVqh4Hp4YDG0qBmktQAOmkKIFKwSBq9H087G9DvqAEEAXlyDwUnqRmA9gDnGbkW6zJe3xOYzRKwBWpTw57K656VuNuLBwONYPWT3fOEoKzbki4pxxNImkD3hYr/tFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Mkts", type text}, {"Dept", type text}, {"SCat", type text}, {"Cat", type text}, {"Seg", type text}, {"Brand", type text}, {"Upc", Int64.Type}, {"4 W/E 10/06/17", type number}, {"4 W/E 11/03/17", type number}, {"4 W/E 12/01/17", type number}})
in
    #"Changed Type1"

Capture.PNG

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
sifar786 Member
Member

Re: Merge or concatenate values in Top 2 Rows into a single Row to Promote as Headers

Hi @v-frfei-msft@Nathaniel_C ,

Thank you all for your valuable inputs. 🙂

Last night after posting, i gave a last try and came up with this solution. Though it is long, it does serve my purpose.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZLdSsNAEIVfpQS8q5vdmUncvczPtgRsI41plZCLoEFFrGL7/jgTi0nBq0I453DIfrNk0jRBMP/nqfdvx8PErzZ99/QatPMmWL0PTd5/HdmqrBP71ap/YU2/u/2znL3LWGm2C/3M6FDHobkZCxNqPCsg1EYKmZGkcvTw+dHLGHPKPETioixztq1fLgtfcXrYJRs2dOjIISdAMMrIu7G1qEg6csrg5fDIUewIBASgUAJwiImDM8rS5Wi+s7XDHSEiVEinLQgwy/0ECCNQYuq3rOtyfc1M+fyJqI7Ikca/VcIAyv1iAsIRJLG+L25lpUmxeWRf1QOHIWAjPf4WbfsD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t]),
    
    // take first 2 rows and transform them
    First2Rows = Table.FirstN(Source, 2),
    TransposeRows2Columns = Table.Transpose(First2Rows),
    AddCustomColumn = Table.AddColumn(TransposeRows2Columns, "Custom", each if [Column1] = "" then [Column2] else [Column1]&"~"&[Column2]),
    RemoveFirst2Columns = Table.RemoveColumns(AddCustomColumn,{"Column1", "Column2"}),
    TransposeColumn2Rows = Table.Transpose(RemoveFirst2Columns),
    SourceWithout2FirstRows = Table.RemoveFirstN(Source, 2),
    CombineRows2Source = Table.Combine({TransposeColumn2Rows,SourceWithout2FirstRows}),
    PromotedFirstRowAsHeaders = Table.PromoteHeaders(CombineRows2Source, [PromoteAllScalars=true])
in
    PromotedFirstRowAsHeaders

The Result is how i expect it:PowerBI test table.JPG

 

Note: I am only transposing the 1st 2 rows as the dataset is huge and it will exceed 16384 columns if i transpose all. Besides, it is also going to hog my memory.

 

A few observations of PowerBI:

  1. Can the steps be shortened or is there a more efficient way of doing this? I have more steps following these steps to do further transformations.
  2. Right now, i am considering only 1st 2 rows. But what if one of the data extracts has more number of null rows on top and some cells may contain text in it? I think the code would fail to determine the Dimensions and Facts columns correctly.
  3. I cannot auto-resize the columns though it does show the resize arrows.
  4. I am unable to enter null values in cells in Power BI > Enter Data.

Can someone address these questions inorder to have a concise, dynamic and efficient solution? I am here to learn!

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors