cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ammartino44
Helper III
Helper III

Modifying Power Bi data tables

If I upload a data table from excel into power bi, can I modify it...in terms of the formulas etc? 

2 ACCEPTED SOLUTIONS
ankitpatira
Community Champion
Community Champion

@ammartino44 You can upload data table from excel in power bi service (check it out here) and make use of it. That won't let you currently edit formulas etc since it is in power bi service. If you meant import data from Excel into power bi desktop using excel connector then yes it is straight import and so you will be able to do all the transformations that you can do in pbi desktop. If you meant importing excel workbook into pbi desktop (here) then yes after import you will be able to use all functionalities of pbi desktop.

View solution in original post

v-shex-msft
Community Support
Community Support

Hi @ammartino44,

 

According to your description, you import an excel workbook to power bi and you want to modify the files during loading, right?

If as I said, you can use query editor to modify these data.

 

For example, the source contains 500 rows, but I want get the top 100 rows.

 

Click edit button:

Capture.PNG

 

Enter the query editor:

Capture2.PNG
 

Click on table button and choose “Keep Top Rows”:

Capture3.PNG
 

Fill in the parameter:

Capture4.PNG
 

Result(keep 100 rows):

Capture5.PNG
 

In addition, you can also directly use power query to current modify the query at “Advanced Editor”.

 

Before keep row, I want to remove the null value records.

 

Open the Advanced Editor:

Capture6.PNG
 

Add custom step:

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxxx\Desktop\test sampel.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"Amount", Int64.Type}}),
    Cutom =Table.SelectRows(#"Changed Type", each [Amount] <> null),
    #"Kept First Rows" = Table.FirstN(Cutom,100)
in
#"Kept First Rows"

 

Result:

Capture7.PNG
 

For detail information about power query, you can refer to below article:

Power Query (informally known as "M") Formula reference

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

Hi @ammartino44,

 

According to your description, you import an excel workbook to power bi and you want to modify the files during loading, right?

If as I said, you can use query editor to modify these data.

 

For example, the source contains 500 rows, but I want get the top 100 rows.

 

Click edit button:

Capture.PNG

 

Enter the query editor:

Capture2.PNG
 

Click on table button and choose “Keep Top Rows”:

Capture3.PNG
 

Fill in the parameter:

Capture4.PNG
 

Result(keep 100 rows):

Capture5.PNG
 

In addition, you can also directly use power query to current modify the query at “Advanced Editor”.

 

Before keep row, I want to remove the null value records.

 

Open the Advanced Editor:

Capture6.PNG
 

Add custom step:

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxxx\Desktop\test sampel.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"Amount", Int64.Type}}),
    Cutom =Table.SelectRows(#"Changed Type", each [Amount] <> null),
    #"Kept First Rows" = Table.FirstN(Cutom,100)
in
#"Kept First Rows"

 

Result:

Capture7.PNG
 

For detail information about power query, you can refer to below article:

Power Query (informally known as "M") Formula reference

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

ankitpatira
Community Champion
Community Champion

@ammartino44 You can upload data table from excel in power bi service (check it out here) and make use of it. That won't let you currently edit formulas etc since it is in power bi service. If you meant import data from Excel into power bi desktop using excel connector then yes it is straight import and so you will be able to do all the transformations that you can do in pbi desktop. If you meant importing excel workbook into pbi desktop (here) then yes after import you will be able to use all functionalities of pbi desktop.

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.