Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

need help in data transform

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

 
1 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

 

View solution in original post

6 REPLIES 6
KBO
MVP

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
Not applicable

@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

KBO
MVP

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.