Transform data

[kokoro_1202]

Hi all,

can anyone help to transform the data? I tried pivoting the table but kept encounting error.

Appreciate if anyone can advice.

https://gofile.io/d/foD9u5

Thank you.

[v-jingzhang]

Hi @kokoro_1202 

If it is possible, you can select either cell in the data and use Ctrl+T keys to format the data into two separate tables in Excel file. Then connect to this Excel file in Power BI Desktop. In this way, Power BI Dekstop will detect these two separate tables. You can import them as two separate queries. This will make the transformation process more easily.

Then in Power Query Editor, you can transform a single query with below codes  easily.

let
    Source = Excel.Workbook(File.Contents("D:\DataForTest\Sales.xlsx"), null, true),
    Table1_Table = Source{[Item="Table3",Kind="Table"]}[Data],
    #"Removed Columns" = Table.RemoveColumns(Table1_Table,{"Total"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Year", each 2018),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Custom",{{"2018", "Shop Name"}}),
    #"Unpivoted Other Columns1" = Table.UnpivotOtherColumns(#"Renamed Columns1", {"Shop Name", "Year"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns1",{{"Attribute", "Month"}})
in
    #"Renamed Columns"

After perform the same operations to the other query, you can combine two queries into one query to analyze the data further. Download the following attached .pbix file for detailed steps.

Kindly let me know if this helps.
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[selimovd]

Hey @kokoro_1202 ,

without the XLSX I cannot really do anything in the Power BI file:

Why don't you just unpivot the columns?

Here a similar example:

Select the first column and chose "Unpivot Other Columns":

 And then you have the data in a format that you can analyze easily :

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[kokoro_1202]

@selimovd 

https://gofile.io/d/JuaCuC This is sample excel 

[selimovd · Accepted solution]

Hey @kokoro_1202 ,

try the following code:

let
    Source = Excel.Workbook(File.Contents("D:\_\Sales.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> null)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Column1", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Custom", each if not Text.StartsWith([Column1], "Shop") then "" else [Column1]),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Conditional Column",{"Custom", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17"}),
    #"Added Conditional Column1" = Table.AddColumn(#"Reordered Columns1", "Custom.1", each if Text.StartsWith([#"Column1"], "Shop") then "" else [#"Column1"]),
    #"Reordered Columns2" = Table.ReorderColumns(#"Added Conditional Column1",{"Custom", "Custom.1", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17"}),
    #"Replaced Value" = Table.ReplaceValue(#"Reordered Columns2","",null,Replacer.ReplaceValue,{"Custom.1"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Custom.1"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filled Down", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"", type text}, {"2018", Int64.Type}, {"2018_1", type text}, {"1/1/2018", type any}, {"2/1/2018", type any}, {"3/1/2018", type any}, {"4/1/2018", type any}, {"5/1/2018", type any}, {"6/1/2018", type any}, {"7/1/2018", type any}, {"8/1/2018", type any}, {"9/1/2018", type any}, {"10/1/2018", type any}, {"11/1/2018", type any}, {"12/1/2018", type any}, {"1/1/2019", type any}, {"2/1/2019", type any}, {"3/1/2019", type any}, {"Total", type any}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type2", each [#""] <> null and [#""] <> ""),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"", "Shop"}, {"2018", "Year"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"2018_1", "Total"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Shop", "Year"}, "Attribute", "Value"),
    #"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Date"}})
in
    #"Renamed Columns1"

The code is not optimized, but I think it should give you the desired result.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[amitchandak]

@kokoro_1202 , I do not think this ideal data format for business intelligence.

M/Power BI Expert can help. @ImkeF , can you help with this.

[kokoro_1202]

yes, understand this format is not ideal, but these are historical data & was linked to other formula so we wonder if we could transform in power bi, without actually changing in excel itself.

[amitchandak]

@kokoro_1202 , Yes, I have marked @ to @ImkeF  for that. Let us see some suggestion from other super users and M experts