Adding separated columns to table where the raw data has columns in one column

[Lizz012]

Hi Power Bi Team

I am trying to create a table from an excel spreadsheet that has months in one column, that should be split into separate columns.The corresponding data(Total sales) along with the months should 'auto' sync, with the corresponding months after the months have been separated.

The excel spreadsheet looks like this for example:

Months                     Total Sales

2023/01/01                  868

2023/02/01                  921

2024/01/01                   678

The table in PowerBi should look like this

Jan'23         Feb'23               Jan'24

868              921                    678

I would also like that should i refresh or update my raw excel data, that the formulas should apply to any new months that i add.

Kindly assist with this query.

Thanks

[Lizz012]

@Akash_Varuna This totally worked ..until one part didnt ...The total sales do not match up according to their particular years....instead they pivot into one finacial year...and does not sync the data according to the year.

What i would like to achieve is that once the years are split into columns the data for the totals sales are able to automatically fill themselves in the appropriate year.

So if i select the year Jan'23 then i want to see an outcome of 868.

Right now what is happening even after pivoting total sales....all the data of total sales is for some reason going into one year?

Is there a trouble shoot for this?

Thank you in advance 🙂

[pankajnamekar25]

Hello @Lizz012 

Here is the code in Power Query

let
Source = Excel.Workbook(File.Contents("C:\Users\inpan\OneDrive\Desktop\Book1.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Months               ", "Months"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Months", type date}, {"      Total Sales", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine({Date.ToText([Months], "MMM"), "'" & Text.End(Date.ToText([Months], "yyyy"), 2)})),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Months"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "      Total Sales", List.Sum)
in
#"Pivoted Column"

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[Lizz012]

Thank you appreciated 🙂

[Akash_Varuna · Accepted solution]

Hi @Lizz012 You can split the Months column into separate Year and Month columns using Power Query’s Split Column function by the delimiter /. Then, format the Year column to show only the last two digits and combine the Year and Month into a new column, like Jan'23. After that, pivot the data by selecting Total Sales as the values column to create separate columns for each month. When new months are added, refreshing the data will automatically apply the transformations and adjust the table.