Forum Discussion

tmcateer4's avatar
tmcateer4
Regular Visitor
5 years ago

Power BI query

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"

Need Help

5 Replies

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    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's avatar
      tmcateer4
      Regular Visitor
      5 years ago

      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!

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      tmcateer4
      Regular Visitor
      5 years ago

      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