Your privacy matters: This site uses cookies to analyze site usage and provide social media features. Learn More.

How Do You Share Data Among Excel, Power BI Desktop, and PowerBI.com?

Excel tables or worksheets and Excel Power Pivot are popular sources of data for Power BI Desktop models. Sometimes, you may also want to view the Power BI Desktop model (or a PowerBI.com data model) back in Excel as a pivot table or simply export a table back to Excel. Here are some of the options.
Steps:

  1. Importing an Excel table or worksheet into a Power BI Desktop data model: Click Get Data, Excel. Once you have chosen the source workbook, you can preview and/or select tables and/or worksheets from the Navigator dialog. Tables have a small blue bar across the top. If a table has the same name as a worksheet, the table will have a numeric suffix (e.g. Customer5 in the screenshot). Load takes you straight to report design, Edit invokes Power Query in case you want to do some data transformations.

    Excel Navigator


  2. Importing an Excel Power Pivot data model into a Power BI Desktop data model: Click the drop-down button to the left of the Home tab. Choose Import, Excel workbook contents.

    Import an Excel workbook


  3. Importing a Power BI Desktop data model table into an Excel table: Open the Power BI Desktop pbix file and leave it open. In Excel, click Add-ins, DAX Studio (you will need to have downloaded and installed the free and wonderful DAX Studio). In the DAX Studio Connect dialog, click PBI / SSDT Model and click Connect – it should automatically pick up the name of the open pbix file. Note the port number after the localhost server name in the status bar, e.g. localhost:55641 – this is needed in step 4. From the Home tab, click Output, Excel, Static. Type an evaluate query for a table of interest (see screenshot) and click Run. Switch to Excel to see the data – you can now make this a table (Ctrl-T) and/or add it to Power Pivot (Power Pivot tab, Add to Data Model). Leave the pbix open for the next step.




  4. Connecting an Excel pivot table to a Power BI Desktop data model: On the Data tab, click Get Data, From Database, From Analysis Services. Enter the server name and port from step 3 (e.g. localhost:55641). Click Next, Finish, PivotTable Report, OK.




  5. Connecting an Excel pivot table to a PowerBI.com data model: In Excel click the Power BI tab (you need to have installed the free Power BI Publisher for Excel). Click Connect to Data. Select one of your PowerBI.com workspaces, click Dataset, choose one of your published datasets and click Connect to see your pivot table in excel. You can also use Analyze in Excel directly from within PowerBI.com.



There are many other options for sharing data between Excel and Power BI that are not covered here. For example, you can publish an Excel pivot table to PowerBI.com.

Contact Us:

Accelebrate’s training classes are available for private groups of 3 or more people at your site or online anywhere worldwide.

Don't settle for a "one size fits all" public class! Have Accelebrate deliver exactly the training you want, privately at your site or online, for less than the cost of a public class.

For pricing and to learn more, please contact us.

Contact Us Train For Us

Toll-free in US/Canada:
877 849 1850
International:
+1 678 648 3113

Toll-free in US/Canada:
866 566 1228
International:
+1 404 420 2491

925B Peachtree Street, NE
PMB 378
Atlanta, GA 30309-3918
USA

Subscribe to our Newsletter:

Never miss the latest news and information from Accelebrate:

Microsoft Gold Partner

Please see our complete list of
Microsoft Official Courses

Recent Training Locations

Alabama

Huntsville

Montgomery

Birmingham

Alaska

Anchorage

Arizona

Phoenix

Tucson

Arkansas

Fayetteville

Little Rock

California

San Francisco

Oakland

San Jose

Orange County

Los Angeles

Sacramento

San Diego

Colorado

Denver

Boulder

Colorado Springs

Connecticut

Hartford

DC

Washington

Florida

Fort Lauderdale

Miami

Jacksonville

Orlando

Saint Petersburg

Tampa

Georgia

Atlanta

Augusta

Savannah

Idaho

Boise

Illinois

Chicago

Indiana

Indianapolis

Iowa

Ceder Rapids

Des Moines

Kansas

Wichita

Kentucky

Lexington

Louisville

Louisiana

Banton Rouge

New Orleans

Maine

Portland

Maryland

Annapolis

Baltimore

Hagerstown

Frederick

Massachusetts

Springfield

Boston

Cambridge

Michigan

Ann Arbor

Detroit

Grand Rapids

Minnesota

Saint Paul

Minneapolis

Mississippi

Jackson

Missouri

Kansas City

St. Louis

Nebraska

Lincoln

Omaha

Nevada

Reno

Las Vegas

New Jersey

Princeton

New Mexico

Albuquerque

New York

Buffalo

Albany

White Plains

New York City

North Carolina

Charlotte

Durham

Raleigh

Ohio

Canton

Akron

Cincinnati

Cleveland

Columbus

Dayton

Oklahoma

Tulsa

Oklahoma City

Oregon

Portland

Pennsylvania

Pittsburgh

Philadelphia

Rhode Island

Providence

South Carolina

Columbia

Charleston

Spartanburg

Greenville

Tennessee

Memphis

Nashville

Knoxville

Texas

Dallas

El Paso

Houston

San Antonio

Austin

Utah

Salt Lake City

Virginia

Richmond

Alexandria

Arlington

Washington

Tacoma

Seattle

West Virginia

Charleston

Wisconsin

Madison

Milwaukee

Alberta

Edmonton

Calgary

British Columbia

Vancouver

Nova Scotia

Halifax

Ontario

Ottawa

Toronto

Quebec

Montreal

Puerto Rico

San Juan

© 2013-2019 Accelebrate, Inc. All Rights Reserved. All trademarks are owned by their respective owners.