Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
PEEVEE
New Member

Unpivoting Multi Columns based on specific text contain in Header (Date)

Hi All,

 

I have a table in excel which is under transformation in Power BI but due to more than 300 column i am facing challenge while unpivoting data manually selection it is taking too much time and vision while selecting columns having words "Date" in it.

 

Below is snap of my TABLE 1

PEEVEE_0-1686387252668.png

above snap is 20th part of my data columns, 

Now, my requirement is to unpivot all columns having date in their headers, similarly will do with amount and so on..

I know there is a way with M Code to unpivot all columns with sepecific text in it.

 

PEEVEE_1-1686387607540.png

 

 

 

But unable to search it online

 

Please help me to find a way to automatic unpivot columns with coding or any other tips/tricks.

 

Thanks in advance.

 

 

 

4 REPLIES 4
PEEVEE
New Member

PEEVEE_0-1686540018598.png

First 2 columns are parts of fact table, so does not require any transformation.

But for all dates and amount, need to unpivot which will be in column3 and column4.

Output should be something similar to below table.

PEEVEE_1-1686540078214.png

Thanks in advance.

 

NewStep=Table.Combine(Table.TransformRows(Table1,each let a=Record.ToTable(_) in Table.Combine(Table.Group(Table.Skip(a,2),"Name",{"n",each #table(List.FirstN(a[Name],2)&List.TransformMany({1..Table.RowCount(_)},each {"Attribute","Value"},(x,y)=>y&Text.From(x)),{List.FirstN(a[Value],2)&List.Combine(Table.ToRows(_))})},0,(x,y)=>Byte.From(Text.EndsWith(y,"Date")))[n]))

this code is not working in my current PBI Power Query, if you have created any sample data for my subjected problem,  request you to please share PBIX sample file for my better understaning on code & its working.

wdx223_Daniel
Super User
Super User

then what the output you expect.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors