Need help in loading this type of data from Excel file

[tarunbisht20001]

I have data in such format in Excel.

Now i wanna load this data in power Bi and do it for latest two months only, dynamically how can i do that?
I am not able to figure out how can I get month, like from this column to this column its may or june and then how can i pivot or unpivot them.

please help!!!

[nandic · Accepted solution]

@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

[Anonymous · Accepted solution]

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.

[nandic · Accepted solution]

@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