Model Data from Input Table

[Anonymous]

Hello,

I am looking for a resource on how to edit data in excel so it is easy to work with in Power BI

My organisation have an input table that looks somewhat like this:

Category	SubCategory	Base	Target			jan	feb	mar	apr	may	jun
Cat 1
	SubCat 1	0	100			0	0	0	25	68	100
	SubCat2	0	100			10	35	60	85	90	100
	SubCat3	0	100			20	40	60	80	100	100
Cat 2
	SubCat1	0	100			0	0	0	0	0	0
	SubCat2	0	100			5	15	25	35	45	55
	SubCat3	0	100			15	30	45	60	75	90

Good for people who are going to input the data but less good for power bi.

I was thinking that it is better to have the data like this:

Category	SubCategory	Base	Target	Month	Value
Cat 1	SubCat1	0	100	jan	0
Cat 1	SubCat2	0	100	jan	10
Cat 1	SubCat3	0	100	jan	20
Cat 2	SubCat1	0	100	jan	0
Cat 2	SubCat2	0	100	jan	5
Cat 2	SubCat3	0	100	jan	15
...	...	...	...	...	...

Do any of you know a guide on youtube on how to do it in excel or power bi?

Or telling me is obviously okay too.

Thanks!

[Vijay_A_Verma]

Below is M code in Power Query. Power Query will have to be used to give a shape to your data.

The Excel file used is uploaded to https://1drv.ms/x/s!Akd5y6ruJhvhuV3qyP0JmyTmxaTo?e=Y9rige 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"Column1", "Column2"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "AllNull", each List.NonNullCount(Record.ToList(_))=0),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom1", each ([AllNull] = false)),
    #"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows1",{"AllNull"}),
    #"Filled Down" = Table.FillDown(#"Removed Columns2",{"Category"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each [SubCategory] <> null),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Target", "Base", "SubCategory", "Category"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Date", each Date.From("1"&[Attribute])),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Date", Order.Ascending}, {"Category", Order.Ascending}, {"SubCategory", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Date"})
in
    #"Removed Columns1"

[Greg_Deckler · Accepted solution]

@Anonymous Easiest thing to do would be on your import to Power BI, select your month columns and then unpivot them.

[Anonymous]

@Greg_Deckler Thank you, this was exactly what I was looking for!