Split data source/query table into tables/querys

[Anonymous]

excel into PBI

Hi,

above is how my power bi data query looks when inmported from excel. Is there a way I can break out this into 2 seperate tables or querys?

I want to split it up but I also want to make sure once data is changed in excel it changes both queries/tables en though they are split up. If possible I would like to split it up into 2 queries in power BI without having to split it up in excel the excel sheet.

[Anonymous]

I would like for rows 2-7 be their own table/query

[Anonymous]

Hey Karla,

Your two queries should be something along these lines.

First Table:

let
    Source = Excel.Workbook(File.Contents("---insert file path---"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Active Projects - 48", type text}, {"Computer Users", Int64.Type}, {"Project IT Staff", Int64.Type}, {"""AS Sold"" Project Cost", Int64.Type}, {"Project IT Budgets Current IT Spend of all Projects", Int64.Type}, {"Current IT Spend of all Projects", Int64.Type}, {"Column7", type text}, {"Column8", type any}, {"Column9", type any}, {"Column10", type text}, {"Column11", type any}}),
    #"Kept First Rows" = Table.FirstN(#"Changed Type", each [#"Active Projects - 48"] <> null),
    #"Removed Other Columns" = Table.SelectColumns(#"Kept First Rows",{"Active Projects - 48", "Computer Users", "Project IT Staff", """AS Sold"" Project Cost", "Project IT Budgets Current IT Spend of all Projects", "Current IT Spend of all Projects"})
in
    #"Removed Other Columns"

Second Table:

let
    Source = Excel.Workbook(File.Contents("---insert file path---"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Kept Last Rows" = Table.LastN(Sheet1_Sheet, each [Column1] <> null),
    #"Promoted Headers" = Table.PromoteHeaders(#"Kept Last Rows", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Mega Projects", type text}, {"Forecast Completion", type date}, {"Project % Complete", Int64.Type}, {"Computer Users", Int64.Type}, {"Craft", Int64.Type}, {"Project IT Staff", Int64.Type}, {"""AS Sold"" Project Cost", type text}, {"Project IT Budgets", Int64.Type}, {"% of ""As Sold"" Contract", Int64.Type}, {"Current IT Spend", type text}})
in
    #"Changed Type"

Please note that this is not the cleanest way to pull in data and you will have to edit the query if columns change.

Kind regards,

Alex

[Anonymous]

As for source,

it's coming for Sharepoint. It's an excel sheet uploaded into SharePoint. But when I get the SharePoint link it doesn't work it makes me narrow it down.

When I inmported the data I went through sharepoint folder and had to search for the excel sheet.

How do I go about the srouce?

[Anonymous]

@Anonymous