need help in data transform

[Anonymous]

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

[KBO]

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

[Anonymous]

@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?

[amitchandak · Accepted 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

[KBO]

@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

[KBO]

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

[amitchandak]

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/