Forum Discussion
Add data column from source database after query is initially created?
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.
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
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.