Microsoft Fabric: How to load data in Lakehouse using Dataflow Gen2| End to End Flow

[@sudarsana1234]

Amit ,The way you are teaching, really easy to understand. will love to explore all 40 videos in this fabric Playlist 👍

[@AmitChandak]

Thank you so much for your kind words! 🙏 I'm happy to hear that you find the videos easy to understand. Enjoy exploring the rest of the series, and feel free to share any questions or feedback you might have. Happy learning! 😊📊👨‍🏫 Mastering Microsoft Fabric 40+ Videos: kzbin.info/www/bejne/pl7ZYXxriJKsmNU

[@sudarsana1234]

@@AmitChandak Thank you Amit 😊. I might need your support and guidance for DP-600🙏

[@mohammadaamirkhan7830]

I learned so many new things on the Microsoft Febric Platform today. Thank you Sir for your detailed videos on this.

[@AmitChandak]

Glad it was helpful!

[@SujathaS-sx3kg]

It was clear and concise explanation about fabric sir

[@Laughing_out_loud_]

I was following this video till 23:03. Created a lake house but accidentally clicked on "Publish Later". Its been over 12 hours and I do not see the tables I added to the Lakehouse. These are the tables from my On Prem SQL server and are fairly large in size. Now I have a couple of questions: 1. What advantage I will have of keep these on the Lakehouse v Keeping them on SQL Server VM? 2. Can I also add Views from SQL server to Lake house and they will perform process data from their respective tables in the Lakehouse? 3. To me its seems like Fabric provides an alternative to everything I used to do with SSIS, SQL server, SQL job agents etc. So if my current system is working why do I need to shift to fabric?

[@AmitChandak]

On the three dots near to Dataflow gen2 name click and check Refresh history , if refresh is not running of failed. manually run it 1. What advantage I will have of keeping these on the Lakehouse v Keeping them on the SQL Server VM? Lakehouse is on the cloud and saves data in Delta parquet format, also it Read only SQL end point, you can use to query data in SQL. Power BI dataset do not copy data, so only one copy 2. Can I also add Views from SQL server to Lake house and they will perform process data from their respective tables in the Lakehouse? If you want to import yes. If you want a shortcut, no as of now 3. To me its seems like Fabric provides an alternative to everything I used to do with SSIS, SQL server, SQL job agents etc. So if my current system is working why do I need to shift to fabric It is a complete analytics system. It can replace many existing systems like SSIS, SQL server, ADLS gen2 etc

[@jatinsaini7773]

What if we give space in the column names in the Lakehouse. Is there any microsoft documentation which recommend this ?

[@AmitChandak]

Space in table name only supported in case of shortcut tables as of now. But for normal table creation space is not supported yet. But in table name space is supported now. %%sql create table `sales lake test` (`id1` int, `name1` varchar(55))

[@adilmajeed8439]

Thanks for sharing. By loading the data through DFG2, the table names are appearing in proper case but if we do the same process through Apache Spark, the table names will always be in lower case? Any reference point ....

[@AmitChandak]

Refer to this community.fabric.microsoft.com/t5/Data-Science/Notebooks-writing-tables-with-names-lowercased-how-to-fix/m-p/3753909

[@riteshkumar4493]

Great explanation, thanks. Just one thing if we use fabric so in that case Power BI Desktop tool is required or not?

[@AmitChandak]

Power BI Desktop tool is not required. But in Case you prefer to create report on Desktop, you can use that.

[@riteshkumar4493]

@@AmitChandak Thanks. It means we can do all things in Microsoft Fabric, whatever we do in Power BI Desktop tool..

[@sanumpzha]

Nice video Amit.👍Loved it.

[@AmitChandak]

Thanks. 🙏 Hope you will like the full series kzbin.info/www/bejne/pl7ZYXxriJKsmNU

[@sanumpzha]

@@AmitChandak will watch the series..👍

[@mohammadaamirkhan7830]

Thank you Sir for making this video. However, I am getting error while at video 32:50 when I add implicit and explicit measure like Gross, Discount in visual in Power BI Web. It is showing error like "Can't display the visual - couldn't load the data for this visual. Please try again later or contact support" Could you please advise on this?

[@AmitChandak]

Please log an issue - community.powerbi.com/t5/Issues/idb-p/Issues. I faced that in past with couple of measures.

[@mohammadaamirkhan7830]

@@AmitChandak Thank you once again for your advise.

[@amaia9476]

Nice video. Is it possible to write data PARTITIONED through Dataflow gen2 into a Lakehouse? I know I can do it with a notebook after data is ingested from source. But what if I want to read data from source through DF Gen2 and write it into the Lakehouse partitioned at the same time?

[@nissimelaluf1272]

Is it possible to Query the SQL Exten about calculated Measure as well like the actual columns ?

[@AmitChandak]

I doubt you can query measure from SQL as of now

[@SujathaS-sx3kg]

Sir I used create a folder for all measures, as they will be in one folder, whether I can create it fabric or I should create it in desktop

[@AmitChandak]

You have create tables in fabric and use those as folders. Like we created a table with name measure with one column and no data and used that as measure folder.

[@stortor-o1m]

How are you supposed to sort the dates this way you were going to save it and the months are not in chronological order

[@AmitChandak]

I usually have month sort and month year sort column to correct the sorting. I might have missed that here.

[@taniyaganguly]

can you help to write query to see datatype of columns from table in fabric

[@AmitChandak]

In case of Lakehouse or Warehouse use SQL End Point and try this query SELECT top 100 * FROM INFORMATION_SCHEMA.COLUMNS

[@taniyaganguly]

@@AmitChandak it worked thank you

[@rayromych5534]

Thanks for the video I used dataflow to add data to a data lake from web api, which worked fine, but then I wanted to switch from "New table" to "Existing table" to add data every day by refreshing the dataflow, but when I go to edit or recrate destination - it doesnt see the table in the data lake that was created by initial refresh :(

[@samirsahin5653]

Thanks, nice video. How to create a scheduled refresh? When I create a scheduled refresh on Dataflow gen 2, it creates new table every time it refreshs. Basically what I want is, import data via Dataflow Gen2 into Lakehouse, transform it (on SQL endpoint, notebook python or Power Query as you done) and then use this data set on PBI. Everything is okay but can't manage the refresh data. When I refresh dataflow gen2 it creates new table which power bi and transform process is not connected to this new table.

[@AmitChandak]

Refer if these two incremental approaches, if these can help Microsoft Fabric Part 15: Incremental Data Load(ETL) for Warehouse using Dataflow Gen 2, SQL Procedure, Data Pipeline: kzbin.info/www/bejne/o6Gpg6GQd72mrLM How about incremental without Dataflow, Only using pipeline and SQL procedure for Microsoft Fabric Warehouse Microsoft Fabric Part 16: kzbin.info/www/bejne/p6SyepmKr892ibM

[@samirsahin5653]

@@AmitChandak Thanks for super quick replay, But I'm a bit confused. What I want to do is refresh table. Why should I go and deal with SQL? Is it not possible just refresh table in Data Flow Gen 2? When I run Dataflow Gen 2, it creates new table because Dataflow Gen 2 that we created is getting data from a file. I think we need to update data via Dataflow Gen2, not get data. Can we not do this without SQL?

[@sonalimishra3356]

I have no data on cloud,what ever i am using that is on sql server ,and have created gateway connection for this,so when i am creating dataflow gen 2 through sql server ,using that on premise gateway getting this error " The selected connection has an on-premises data gateway that is different than the project's gateway. Committing will apply to all queries in this mashup" what to do next?

[@AmitChandak]

Please check this video in the series. kzbin.info/www/bejne/pXapXp1-i9KpmtE Always use latest on-premise gateway. If Dataflow uses already existing connection and not working, Then use new connection and update the details again.

[@saketsrivastava84]

Sir fabulous video, if possible create video on importing data from excel or migrating data from on premise data source into fabric.

[@AmitChandak]

Thanks. Check this video for excel - kzbin.info/www/bejne/hpqYeqmhfd19eNU

[@Logicbi]

Amit, really appreciate your way of teaching... I am not able to find perfect video on Git integration in power bi, version control and multiple users working on same power bi report, how to merge and manage conflicts can you please make video on it.

[@AmitChandak]

I need to create one yet. But please watch this from guyinacube - kzbin.info/www/bejne/sKfco2WnjqiEhNE

[@sonalimishra3356]

Instead of data lake house can we create all in ware house ?

[@AmitChandak]

Yes, We can do an end-to-end flow on the Warehouse too. Dataflow Gen2, Data Pipeline sqn SQL can write to Warehouse. As of now, notebooks do not write directly to Warehouse. I have covered the flow in the series. Initially, the Warehouse destination was not available to I used an alternative. But now you can use warehouse destination Mastering Microsoft Fabric 35+ Videos: kzbin.info/www/bejne/pl7ZYXxriJKsmNU

[@RadimStraka-w3n]

Hi Amit, please, will be dataflow gen 2 available also in regular Power BI or it will be exclusive to Fabric? Thank you!

[@AmitChandak]

I checked as of now it is not available. I have asked the question on forum. I will update once I see any replay.

[@RadimStraka-w3n]

@@AmitChandakthank you very much!

[@sohailansari9337]

Awesome explanation Amit bhai👏, I am little confused between the data lake and warehouse.. Can you do a detailed video about it would be awesome.. 😊

[@AmitChandak]

Please refer Microsoft Fabric: Various Components| Microsoft Fabric Create Workspace, Lakehouse, Warehouse- kzbin.info/www/bejne/pofZp5Vjgt-rgdk Microsoft Fabric-What it is, Why Use, Who Should, When and How to use kzbin.info/www/bejne/qZO6f32Dltijm6M

[@sohailansari9337]

@@AmitChandak thanks

[@sonalimishra3356]

If we are using sql server to get data and if it have space then what to do?

[@AmitChandak]

You can rename those columns in Dataflow gen 2(remove spaces), if destination is Lakehouse.

[@sonalimishra3356]

@@AmitChandak even we can use ware house also right

[@SujathaS-sx3kg]

Sir can I create a new table for measures in power bi fabric lakehouse, if yes can you explain the steps

[@AmitChandak]

Create an empty table. And move you measures there and hide the column of the table. For Lakehouse use Spark SQL for Warehouse you can Use SQL endpoint(Warehouse) Create table Measure (column1 varchar(5))

[@SujathaS-sx3kg]

How to create empty table sir

[@SujathaS-sx3kg]

In power Bi fabric I am able to create a measures, but not able to create new column as we create in powerbi desktop. Please explain how to create a column in power bi fabric

[@ankittripathi8305]

hello, I have added data into lakehouse through Gen2 from sharepoint and then want to model the tables, however for two tables, I need many to many relationship, but there is no option for this, how can i achieve this in fabric? Also if I connect the model to Power Bi desktop and want to change the relationship to many to many it would be converted to import locale mode, however i want it in direct/live connection, your inputs please thanks

[@AmitChandak]

Please create a custom semantic model; you can have it there. But be careful, as many-to-many relationships sometimes do not work as expected. I have already shared this feedback with support team.

[@ankittripathi8305]

@@AmitChandak thanks for reply actually we are migrating old semantic models to fabric, yes we will try to be careful with many to many relationship as do we in past. Thanks amit

[@Akn876]

Hi Amit please make a video on how to create power bi data warehouse and difference between lakehouse and datawarehouse

[@AmitChandak]

Warehouse I will load tomorrow. For difference refer Microsoft Fabric: Various Components| Microsoft Fabric Create Workspace, Lakehouse, Warehouse- kzbin.info/www/bejne/pofZp5Vjgt-rgdk Microsoft Fabric-What it is, Why Use, Who Should, When and How to use kzbin.info/www/bejne/qZO6f32Dltijm6M

[@chioma_okoye]

Hi Amit, i am getting an error message "THE GIVEN DATA SOURCE KIND IS NOT SUPPORTED" can you help me out please?

[@AmitChandak]

Please share more details with me. You can ping me LinkedIn - www.linkedin.com/in/amitchandak78/

[@JayPatel-hd8yz]

Hello Amit, I am a new member to your channel as I was following the same steps of loading csv file through WEB API options but its fails stating "Files contains corrupted data" can you please update the file. Thanks

[@AmitChandak]

I tried this file again, and it worked for me. However, some users in the past have also complained that the Git file is not accessible, so giving file at Drobox too: github.com/amitchandakpbi/powerbi/raw/main/Sales%20Data%20for%20Fabric.xlsx Please download this file from Dropbox, and you can upload it to Fabric Lakehouse or OneDrive to use it. www.dropbox.com/s/z9d6qfah6451ct3/retailData_fabric.csv?dl=0 www.dropbox.com/scl/fi/ru5jusnq4c89bsbhganm0/Sales-Data-for-Fabric.xlsx?rlkey=e62al8qyleagpfm4ryggtwkvx&dl=0

[@SethulakshmiPS-r4k]

Hi Amit. Great Job. I have one doubt I am getting below error, can you help me with this? Error Details: Error: Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: Microsoft SQL: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) Details: DataSourceKind = Lakehouse;DataSourcePath = Lakehouse;Message = A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server);ErrorCode = -2146232060;Number = 53;Class = 20 GatewayObjectId: 6309202d-8f81-4235-9651-68924de52acf. Request ID: 490256f4-86ec-4c9b-a71f-83515aa6f59f. (Request ID: c5b3731b-2a50-4ee7-a227-03045b44d390).

[@AmitChandak]

Is it Azure SQL or SQL server? If this on-premise SQL server Microsoft Fabric Part 17: Load local SQL server data in Lakehouse using on-premise Gateway and Dataflow Gen2: kzbin.info/www/bejne/pXapXp1-i9KpmtE For last few days this one was not working (Using on-premise gateways)

[@SethulakshmiPS-r4k]

@@AmitChandak Thank you so much, I am using an on premise gateway, and as you said its not working for last few days. I ll go through the video, Please do make more videos.