Multiple Question on First PBI report: using Extract/Range, IF, Date formatting, Appending, & Charts

[jpalaci1]

I’m attempting my first Power BI report. I take a Excel file export from SAP, transform the data, and the create some Matrix visuals. Where I get tripped up is can I complete all in PQ? Do I do some in PQ then in DAX? I'm not sure the order of how to complete and seem all over the place when completing

I was able to get to my final goal but I had to go into Excel and prepare the data and hope it’s something I can do in Power Query.

The issues I run into in my Sample Data attachment (I completed in Excel to illustrate where I want to go in the two green tabs):

 1. Extract Account Numbers (column A) and add to new column

1. I attempt to take the system # that’s in Account Numbers (column A) but it’s stored as a text and I attempted to remove the two underscore delimiters then convert to numbers but it will revert back to a text and I get stuck there. I fixed this in Excel but not sure how to do in PQ. The desired result is from 1_200100_94000 and all other values are useless and can be filtered out but when I remove I use the range but still stores as text

 2. Merge queries after getting system # from step 1

1. I use that value to do a merge query with the mapping to get the two other columns I need but can’t due to not being able to get the system #. Again, fixed in Excel in real file to get there but want to know how to get in PQ. My Delete or Keep (column L) I can drop by do a filter when it works.

 3. Create column that looks at Account (column G) and sees the first two characters and if it is “PC” then label as Intercompany Purchase and the rest can be left blank or with a dash as a filler

1. Intercompany, I’m not sure how to look at column G (Account) and do a left( for the first two to find “pc”. I’m not sure how to do that in Power Query. Do I transform my data then do this in with DAX because I did find that to be just like Excel but I wasn’t sure if that’s the proper steps to complete.

4. Raw data comes out as YYYY/MM (Year/Month column F) and want to keep that format

1. I run into issue for column F (Year/Month) where I want it to show as YYYY/MM but I can’t seem to get it away from short date format.

 5. Add that date as the report run data and add to previous weeks data as the report is a running total

1. My Date Added I’m not sure how to add the day the report is run and add. I can with a column from examples but how do I do this when I append and add to the last week and the database of old data to do comparison?

6. My question on my visuals, on a Matrix chart, How do I make to show like a pivot table would in classic view rather than the step view with the expand button (sorry for not including photos as I can’t with the real data)

Thank you for any help.

https://bit.ly/3bIBONE (link to Excel file)

[jpalaci1]

Thank you so much! This worked in my file I added

What's the software you use to screen record? It allows the zooming and the highlighting the pointer?

I ran into the issue with real data as the 1,000 line preview only shows that and the steps you showed me for Q1 and Q3 can't be done and other than fixing in Excel, I wasn't able to split it or anything else.

Again, thank you so much!

[VijayP]

@jpalaci1 You can view all records as well by clicking on the status bar below as shown in the picture

I have recorded screen using FasStone and used basic screen magnifier in Windows 10 using   + "+"

[VijayP]

@jpalaci1 

If you find my answer as solution please mark this as solution ! Thanks

[VijayP · Accepted solution]

@jpalaci1 

I have shown this in Excel Power Query , but you can follow the same method in PQ in Power BI

Hope this resolves your issues. Share your Kudos

Q1 - https://drive.google.com/file/d/1F6rw-6t0yGmT3oNJxLFF1pU8L_T6L3g9/view?usp=sharing

Q2 - https://drive.google.com/file/d/1xHwtwyKqNVzo3iqKBY3XH1o1GCfpZCC_/view?usp=sharing

Q3 - https://drive.google.com/file/d/1RrFBwvyOdSNTWAUJLiOCjYCXkopIyxRh/view?usp=sharing

Q4 - https://drive.google.com/file/d/1ACPw01E9eoP_BIOaq6VgnpRblY0tQ9St/view?usp=sharing