Need help in loading this type of data from Excel file

tarunbisht20001 
These two clips will help you see how to unpivot data from Excel in Power Query:
https://www.youtube.com/watch?v=QbRgeskSn0U
https://www.youtube.com/watch?v=O2d5Ec10E1E

To get only the latest 2 months, you can add additional column which will return 1st or the last day for each month.
Example:
May -> 5/1/2024
June -> 6/1/2024
July -> 7/1/2024

And then create filter based on that field to return last 2 months.

 

Cheers,
Nemanja Andic

Hi tarunbisht20001 ,

 

Thanks for reaching out to our community.

Here's the workaround. I created a sample .xlsx file for reference.

After I import it to Power BI Desktop, it looks like below.

The main idea is to transpose first, then replace all the ColumnX values in the month column with null, and then fill it down. The final result is then obtained through pivot columns and unpivot columns

let
    Source = Excel.Workbook(File.Contents("C:\Users\v-stephentao\Downloads\need help in loading this type of data from excel file.xlsx"), null, true),
    Tabelle1_Sheet = Source{[Item="Tabelle1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Tabelle1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"May", type any}, {"Column2", type any}, {"Column3", type any}, {"Jun", type any}, {"Column5", type any}, {"Column6", type any}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    Custom1 = Table.TransformColumns(#"Transposed Table",{"Column1", each if Text.Contains(_,"Column") then null else _}),
    #"Filled Down" = Table.FillDown(Custom1,{"Column1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Column3"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Column1", "Column2"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Column2]), "Column2", "Value", List.Sum),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Attribute"})
in
    #"Removed Columns1"

Final result:

 

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.