How to move your local Datasource (.csv) to Azure Data Lake

[sahilpruthi]

How to move your local Datasource (.csv) to Azure Data Lake such that we can use already created .pbix file.

[vanshrawat]

Check out the links to watch your favourite TV show online
The Leftovers Season 3 Online
The Leftovers Season 3 Episode 1 Online

[vanshrawat]

Check out the links to watch your favourite TV show online
The Leftovers Season 3 Online
The Leftovers Season 3 Episode 1 Online

[ipllive]

IPL 10 Players Auction 2017

[v-haibl-msft]

@sahilpruthi

If the source of already created .pbix file is .csv file in your local computer, after you upload this .csv file to Azure Data Lake, you can go to Power BI Query Editor and change the power query in Advance Editor. We just need to change the source query as below.

let
    Source = DataLake.Contents("adl://herbert.azuredatalakestore.net"),
    #"How to move your local Datasource ( csv) to Azure Data Lake csv" = Source{[Name="How to move your local Datasource (.csv) to Azure Data Lake.csv"]}[Content],
    #"Imported CSV" = Csv.Document(#"How to move your local Datasource ( csv) to Azure Data Lake csv",[Delimiter=",", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV"),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Sales", Int64.Type}})
in
    #"Changed Type"

let
    Source = Csv.Document(File.Contents("C:\How to move your local Datasource (.csv) to Azure Data Lake.csv"),[Delimiter=",", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Sales", Int64.Type}})
in
    #"Changed Type"

Best Regards,

Herbert

[sahilpruthi · Accepted solution]

Thanks Herbert

One more thing I want to confirm is the transformation I have applied to data in .pbix needs to be done again?

[v-haibl-msft]

@sahilpruthi

No, you only need to update the source lines which marked as yellow, and keep the remaining transformation lines.

Best Regards,

Herbert

[sahilpruthi]

Earlier i was using this :

let
Source = Csv.Document(File.Contents("C:\Users\sysadmin\Desktop\db\ipl\deliveries.csv"),[Delimiter=",", Columns=21, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"match_id", Int64.Type}, {"inning", Int64.Type}, {"batting_team", type text}, {"bowling_team", type text}, {"over", Int64.Type}, {"ball", Int64.Type}, {"batsman", type text}, {"non_striker", type text}, {"bowler", type text}, {"is_super_over", Int64.Type}, {"wide_runs", Int64.Type}, {"bye_runs", Int64.Type}, {"legbye_runs", Int64.Type}, {"noball_runs", Int64.Type}, {"penalty_runs", Int64.Type}, {"batsman_runs", Int64.Type}, {"extra_runs", Int64.Type}, {"total_runs", Int64.Type}, {"player_dismissed", type text}, {"dismissal_kind", type text}, {"fielder", type text}})
in
#"Changed Type"

After updating the connection for Data lake the updated string is:

let
Source = DataLake.Contents("adl://optimus.azuredatalakestore.net"),
#"deliveries csv" = Source{[Name="deliveries.csv"]}[Content],
#"Imported CSV" = Csv.Document(#"deliveries csv",[Delimiter=",", Columns=21, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"match_id", Int64.Type}, {"inning", Int64.Type}, {"batting_team", type text}, {"bowling_team", type text}, {"over", Int64.Type}, {"ball", Int64.Type}, {"batsman", type text}, {"non_striker", type text}, {"bowler", type text}, {"is_super_over", Int64.Type}, {"wide_runs", Int64.Type}, {"bye_runs", Int64.Type}, {"legbye_runs", Int64.Type}, {"noball_runs", Int64.Type}, {"penalty_runs", Int64.Type}, {"batsman_runs", Int64.Type}, {"extra_runs", Int64.Type}, {"total_runs", Int64.Type}, {"player_dismissed", type text}, {"dismissal_kind", type text}, {"fielder", type text}})
in
#"Changed Type"

But I am still getting an error that "match_id" not found. Please guide me.

[v-haibl-msft]

@sahilpruthi

It seems that the “match_id” is a column name. Can you see the match_id column in your query table with just following queries? If not, could you please post a screenshot to me about what you get in your table?

let
Source = DataLake.Contents("adl://optimus.azuredatalakestore.net"),
#"deliveries csv" = Source{[Name="deliveries.csv"]}[Content],
#"Imported CSV" = Csv.Document(#"deliveries csv",[Delimiter=",", Columns=21, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source),
in
#"Promoted Headers "

Best Regards,

Herbert

[sahilpruthi]

Resolved.

Thanks a ton.

[sahilpruthi]

Please also let me know data lake auto refresh data or not?