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
i have a worksheet that consists of 2 tables, one is a sale table, another one is the no. of customers visted.
Is there a way to transform the data without modifying the excel? I tried but coudn't differentiate between the sales and visit table.
Here the excel source
Any advice is grateful. Thank you
Solved! Go to Solution.
@Anonymous ,
The format excel is not suitable. But it can work if sales and visits are separate.
I have done Unpivot, and merge and create two final tables Sales and Visit.
Now you can join them with the common date and place dimension and work
Excel:
https://www.dropbox.com/s/gczqt2p8a9335nv/mysalesdata.xlsx?dl=0
Power bi :https://www.dropbox.com/s/aglpzwrb8a4qfm7/mergeData.pbix?dl=0
Refer: https://radacad.com/pivot-and-unpivot-with-power-bi
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
Hi @Anonymous ,
as an addition to the answer befor here is a little code from the advenced editior (power query) which you can use in the first step to make it a little bit easier to you 🙂
let
Source = Excel.Workbook(File.Contents("your path to the excel\mysalesdata (1).xlsx"), null, true),
#"2014_Sheet" = Source{[Item="2014",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"2014_Sheet",{{"Column1", type text}, {"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}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",8),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Visit", type text}, {"01.04.2014", Int64.Type}, {"01.05.2014", Int64.Type}, {"01.06.2014", Int64.Type}, {"01.07.2014", Int64.Type}, {"01.08.2014", Int64.Type}, {"01.09.2014", Int64.Type}, {"01.10.2014", Int64.Type}, {"01.11.2014", Int64.Type}, {"01.12.2014", Int64.Type}, {"01.01.2015", Int64.Type}, {"01.02.2015", Int64.Type}, {"01.03.2015", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Visit"}, "Attribute", "Value")
in
#"Unpivoted Columns"
Best,
Kathrin
@amitchandak @KBO
I am trying to tranform the 2 table into 1 data table in power bi and use it in the visual to show the no. of sales and visits.
Every year, the No. of street is also different (for example in 2014, there are 5 shops and in 2016, there are 6 shops)
Is that possible?
@Anonymous ,
The format excel is not suitable. But it can work if sales and visits are separate.
I have done Unpivot, and merge and create two final tables Sales and Visit.
Now you can join them with the common date and place dimension and work
Excel:
https://www.dropbox.com/s/gczqt2p8a9335nv/mysalesdata.xlsx?dl=0
Power bi :https://www.dropbox.com/s/aglpzwrb8a4qfm7/mergeData.pbix?dl=0
Refer: https://radacad.com/pivot-and-unpivot-with-power-bi
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
@Anonymous,
I think you need to mark the table as table in excel - then I would say yes you can append or merge the easily :).
Best,
Kathrin
Hi @Anonymous ,
have shaped your data a little bit, so that you can work with:
let
Source = Excel.Workbook(File.Contents("path of your excel file"), null, true),
#"2014_Sheet" = Source{[Item="2014",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"2014_Sheet",{{"Column1", type text}, {"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}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",8),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Visit", type text}, {"01.04.2014", Int64.Type}, {"01.05.2014", Int64.Type}, {"01.06.2014", Int64.Type}, {"01.07.2014", Int64.Type}, {"01.08.2014", Int64.Type}, {"01.09.2014", Int64.Type}, {"01.10.2014", Int64.Type}, {"01.11.2014", Int64.Type}, {"01.12.2014", Int64.Type}, {"01.01.2015", Int64.Type}, {"01.02.2015", Int64.Type}, {"01.03.2015", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Visit"}, "Attribute", "Value")
in
#"Unpivoted Columns"
it is only a little modification, with the links you get here you can refine it ;).
Best,
Kathrin
Refer:
https://docs.microsoft.com/en-us/power-bi/desktop-shape-and-combine-data
https://agilethought.com/blog/articles/extract-transform-load-date-power-bi/
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |