Help Power Query Unpivoting Table

I have the table bellow and I want to Unpivot in Power Query.

Can someone help me? It’s just and example, but I will combine multiple files with multiple headers with dates.

File1.xlsx (10.4 KB)

Thanks

1 Like

@Lorennzo_empowerdata ,

Welcome to the forum – great to have you here!

In order to get a proper response on this one, you’ll need to provide a mockup of the result you’re looking for, as there are scores of different unpivot combinations that could be employed here.

Once we have a better idea of the specific outcome you’re looking for, I’m sure we can provide a specific solution.

Thanks!

– Brian

Hi @Lorenzo_empowerdata,

I used your file and unpivoted the date columns, is this what you were after?
Demo.xlsx (24.7 KB)

2 Likes

Actually if you check the rows there are 3 “headers” with different dates. The way you did you filtered the other rows and eliminate all July Dates from the data set.

I receive one file every month with different dates and I want to combine them automatically, But I need to find a way to unpivot each table at once.

Thanks.

1 Like

Sorry Brian it’s my first time here. Thanks for your quick response.

Basically I receive a file every month with new dates and I need to combine the files into Power Query unpivoting the columns from each file and merge then in a single fact table.

I’ve attached the files I receive (demo_rev.xlsx) and the result I want (result.xlsx).

Thanks for helping.

Hi @Lorennzo_empowerdata ,

Based on that information I’ve split up your sample file into separate files and places them in a folder called: ExampleFromFolder

Next I created a Parameter called FolderLocation and used that in the Get Data from Folder, selected the Combine & Transform Data option.

and added a step to the Transform Sample File query

Table.UnpivotOtherColumns(
  Table.RenameColumns( Table.Skip(Sheet1_Sheet, 1), 
    List.Zip(
      {
        Table.ColumnNames(Sheet1_Sheet), 
        List.Transform(Record.ToList(Sheet1_Sheet{0}), Text.From)
      }
    )
  ), 
  {"HOSPITAL", "PROCEDIMENTO", "DIAGNOSTICO"}, 
  "Attribute", 
  "Value"
)

Here are the separated files:
File1.xlsx (9.4 KB)
File2.xlsx (9.4 KB)
File3.xlsx (9.4 KB)

and the solution file
eDNA - Unpivoting Table.pbix (19.7 KB)

I hope this is helpful

2 Likes

Hi @Lorennzo_empowerdata, did the response provided by @BrianJ and @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

I hope that you are having a great experience using the Support Forum so far. Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!

Hi @Lorennzo_empowerdata, we’ve noticed that no response has been received from you since July 9th. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

Hi @Lorennzo_empowerdata, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

Sorry for the late repply. I’ve got some personal issues!
I’ll try the solution and get back to you ASAP.

Thank you so much!