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

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

Accepted Solutions
ankitpatira Super Contributor
Super Contributor

Re: Modifying Power Bi data tables

@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

Highlighted
Community Support Team
Community Support Team

Re: Modifying Power Bi data tables

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 Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

2 REPLIES 2
ankitpatira Super Contributor
Super Contributor

Re: Modifying Power Bi data tables

@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

Highlighted
Community Support Team
Community Support Team

Re: Modifying Power Bi data tables

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 Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 325 members 3,559 guests
Please welcome our newest community members: