Forum Discussion
Add data column from source database after query is initially created?
I havea PBIX, and have created a query joined against two other tables. The majority of the columns in the query are straight columns from a data base source. I've learned I need to add another column from that database source. How do I do that? For the life me I can't find how to just modify the query from the "relationships" page. I would think I should be able to just modify and add that column to the rest of the columns. Any insights? I've searched high and low. Its not a calculated column or anything like that, its just another column from the table my query is using that currently has 7 or 8 of its columns already. I wish I would've loaded all of them initially to prevent this.
I went into Advanced Editor after opening "edit queries".
1) Bolded is what I inserted into the top line:
let
Source = Sql.Database("dbserver", "db", [Query="SELECT #(lf) [SRV_RQST_B_NUMBER_DESC]#(lf),[SRV_RQST_B_STATE_DESC]#(lf),Then i inserted the below line into the columns where they're listed. Apparently #1 and #2 are required.
2)
SRV_RQST_B_STATE_DESC = Table.AddColumn(SRV_RQST_B_STATE_DESC),
Then i clicked OK and it showed up in the table under the query.
6 Replies
Any chance you can post the query from Advanced Editor in Query Editor? You should be able to open Query Editor, refresh the preview and go from there, making certain that your query does not remove other columns, for example. I'd have to see the query to know where things might be going wrong. Sometimes, with CSV files for example, you have to edit the source row to specify increasing the column count.
Yes sir...i wondered if there was some syntax I needed to add in the query to pull in that additional column?
let
Source = Sql.Database("servername", "DB"),
NOWODS_V_NOW_INCD_INCIDENT = Source{[Schema="NOWODS",Item="V_NOW_INCD_INCIDENT"]}[Data],
#"Filtered Rows" = Table.SelectRows(NOWODS_V_NOW_INCD_INCIDENT, each [NOW_INCD_B_DV_ASSIGN_GROUP_TXT] = "Group Name 3" or [NOW_INCD_B_DV_ASSIGN_GROUP_TXT] = "Group Name" or [NOW_INCD_B_DV_ASSIGN_GROUP_TXT] = "Group Name 2"),
CurrentDate = Table.AddColumn(#"Filtered Rows", "CurrentDate", each DateTime.Date(DateTime.LocalNow())),
IncidentResolvedDate = Table.AddColumn(CurrentDate, "IncidentResolvedDate", each if [NOW_INCD_B_RESOLVED_AT_DT] = null then [CurrentDate] else [NOW_INCD_B_RESOLVED_AT_DT]),
#"Changed Type5" = Table.TransformColumnTypes(IncidentResolvedDate,{{"IncidentResolvedDate", type date}}),
DurationDaysOpen = Table.AddColumn(#"Changed Type5", "DurationDaysOpen", each Duration.TotalDays([IncidentResolvedDate] - [NOW_INCD_B_OPENED_AT_DT])),
#"Same Day" = Table.AddColumn(DurationDaysOpen, "Same Day", each if [DurationDaysOpen] <= 1 then 1 else 0),
#"1-2 Days" = Table.AddColumn(#"Same Day", "1-2 Days", each if ([DurationDaysOpen] >1 and [DurationDaysOpen] <=2) then 1 else 0),
#"Changed Type2" = Table.TransformColumnTypes(#"1-2 Days",{{"1-2 Days", Int64.Type}}),
#"2-5 Days" = Table.AddColumn(#"Changed Type2", "2-5 Days", each if [DurationDaysOpen] >2 and [DurationDaysOpen] <=5 then 1 else 0),
#"Changed Type1" = Table.TransformColumnTypes(#"2-5 Days",{{"2-5 Days", Int64.Type}}),
#"5 - 7 Days" = Table.AddColumn(#"Changed Type1", "5 - 7 Days", each if [DurationDaysOpen] >5 and [DurationDaysOpen] <=7 then 1 else 0),
#"7 - 14 Days" = Table.AddColumn(#"5 - 7 Days", "7 - 14 Days", each if [DurationDaysOpen] >7 and [DurationDaysOpen] <=14 then 1 else 0),
#"Changed Type4" = Table.TransformColumnTypes(#"7 - 14 Days",{{"7 - 14 Days", Int64.Type}}),
#"14 - 28 Days" = Table.AddColumn(#"Changed Type4", "14 - 28 Days", each if [DurationDaysOpen] >14 and [DurationDaysOpen] <=28 then 1 else 0),
#"Changed Type3" = Table.TransformColumnTypes(#"14 - 28 Days",{{"14 - 28 Days", Int64.Type}}),
#"28 - 60 Days" = Table.AddColumn(#"Changed Type3", "28 - 60 Days", each if [DurationDaysOpen] >28 and [DurationDaysOpen] <=60 then 1 else 0),
#"60+ Days" = Table.AddColumn(#"28 - 60 Days", "60+ Days", each if [DurationDaysOpen] >= 60 then 1 else 0),
#"Changed Type" = Table.TransformColumnTypes(#"60+ Days",{{"Same Day", Int64.Type}, {"5 - 7 Days", Int64.Type}, {"28 - 60 Days", Int64.Type}, {"60+ Days", Int64.Type}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each true)
in
#"Filtered Rows1"Based on your feedback, I was able to get the right logic in my Advanced Editor. With that, thank you for your solution.
Hi MattAdams,
Glad to hear that you have resolved it. How did you add another column from source database finally? Would you please share the final Power Query to show us the solution? That case, more users may benefit from here.
Thanks,
Yuliana Gu
