Forum Discussion
Split data source/query table into tables/querys
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.
4 Replies
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