Power BI query

[tmcateer4]

This is my query... (the problem is I need to combine CUSTOMERACCOUNT numbers beginning in 90, 900 or 9 with it's matching account 880, 34 or 1178 but dropping the 90, 900 or 9. I tried using replace text but that didn't work.

let
Source = Sql.Database("hadbax1", "ProductionAX2012"),
dbo_HOS_SALEHST = Source{[Schema="dbo",Item="HOS_SALEHST"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_HOS_SALEHST, each [INVOICEDATE] >= #datetime(2020, 1, 1, 0, 0, 0) and [INVOICEDATE] <= #datetime(2021, 12, 31, 0, 0, 0)),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([ITEMCLASS] <> "2100" and [ITEMCLASS] <> "2101" and [ITEMCLASS] <> "2102" and [ITEMCLASS] <> "2103" and [ITEMCLASS] <> "2104" and [ITEMCLASS] <> "2106" and [ITEMCLASS] <> "2150" and [ITEMCLASS] <> "3920" and [ITEMCLASS] <> "9900" and [ITEMCLASS] <> "9999")),
#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each [INVOICEDATE] >= #datetime(2021, 1, 1, 0, 0, 0) and [INVOICEDATE] <= #datetime(2021, 12, 31, 0, 0, 0)),
#"Filtered Rows3" = Table.SelectRows(#"Filtered Rows2", each ([ITEMCLASS] <> "")),
#"Filtered Rows4" = Table.SelectRows(#"Filtered Rows3", each [INVOICEDATE] >= #datetime(2021, 1, 1, 0, 0, 0) and [INVOICEDATE] <= #datetime(2021, 12, 31, 0, 0, 0)),
#"Filtered Rows5" = Table.SelectRows(#"Filtered Rows4", each ([CUSTOMERCLASS] = "DS") and ([COMPANYNUM] = "01"))
in
#"Filtered Rows5"

[watkinnc]

Make sure that the CUSTOMERACCOUNT column is type text, then try typing this into the formula bar:

Table.TransformColumns(PriorStepName, {{"CUSTOMERACCOUNT", each Text.Range(_, 0, 2)}})

--Nate

[tmcateer4]

Thank you for trying. But when I go to that column, it is filtered and shows

 = Table.SelectRows(#"Filtered Rows4", each ([CUSTOMERCLASS] = "DS") and ([COMPANYNUM] = "01"))

I did try adding it but nothing worked. Thanks.

Tracy

[AlB]

Hi @tmcateer4 

Do share some sample input data and the expected output for it. It will help clarify what you need.

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[tmcateer4]

This is an example of some of the data. The CUSTOMERACCOUNT includes a possible two or more account numbers such as 90034 or 34 and 90880 and 880. I want to drop the first three to 34 or first two numbers to 880 respectively whereas I can sum the net sales with the account numbers to one account number. How would I do that? Thanks so much!

[tmcateer4]