Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
can anyone help to transform the data? I tried pivoting the table but kept encounting error.
Appreciate if anyone can advice.
Thank you.
Solved! Go to 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.
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.
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 :
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.
@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.
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.
@kokoro_1202 , Yes, I have marked @ to @ImkeF for that. Let us see some suggestion from other super users and M experts
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |