Power Query Possibility

[ArunKumar3333]

I have a question below is the sample dataset , i want to append all the tables , so the result will be similar to the another pic , is this possibile , can anyone share me the steps pls 

 result , like this for other companies and month and sub abc , def , ghi too 

[Ashish_Mathur]

Hi,

Share the download link of the MS Excel file with 3 tabs - one for each tables that you have shown in your image.

[ArunKumar3333]

is it possible for you to share the pbix file too pls ?

[JoeBarry]

Hi @ArunKumar3333 

Import the Excel as it is.

• Remove the first Row with Year and the Months in it
• Then choose Use First Row as headers. Rename each column to ABC-1, DEF-1, GHI-3 ..... the number represents the month number.
• Highlight all columnns except for the customer column and right click and choose unpivot columns
• Add a custom column and call it year and type in the Year convert to text
• Then highlight the column called Attribute, this should contain the Column name and month number.
• Go to the transform tab and choose splitt by delimter. It automatically chooses - as the delimter but if it doesn't please choose this. This will create an Attribute.2 column.
• We now need to create a date column. Add a custom column and name it Date
• Add this code

"01/" & [Attribute.2] & "/" & [Year]​

• Convert to date

Repeat for the other tables and make sure that all column names are the same in each table and Append all the tables together.

Thanks

Joe

If this post helps, then please Accept it as the solution