Replace Address Sufixes in Power Query

Save you some typing... use find and replace with your column name.

 

 

Table.ReplaceValue(#"Col 1", each [YOURCOLUMN],

each if [YOURCOLUMN] ="STREET" then "ST"

else if [YOURCOLUMN] ="ROAD" then "RD"

else if [YOURCOLUMN] ="DRIVE" then "DR"

else if [YOURCOLUMN] ="COURT" then "CT"

else if [YOURCOLUMN] ="LANE" then "LN"

else if [YOURCOLUMN] ="BOULEVARD" then "BLVD"

else if [YOURCOLUMN] ="EXPRESSWAY" then "EXPY"

else if [YOURCOLUMN] ="EXTENSION" then "EXT"

else if [YOURCOLUMN] ="FREEWAY" then "FWY"

Trim Spaces out of concat column with this code DAX - Power Query

Use this is you are concating multi columns and need to remove unwanted spaces from the concat column

IE: I was concating address fields like so 
[ITSP Street Address] & " " & [ITSP Apartment Number] & " " & [ITSP City] & " " & [ITSP State] & " " & [ITSP Zip Code]

Sometimes the [ITSP Apartment Number] column did not have a value so it added an extra space for the column.

Here's how you get rid of the extra spaces with a Trim.

 

Column names for document library "Name" column

<ViewFields>
<FieldRef Name="LinkFilename"/>
<FieldRef Name="FileLeafRef"/>
<FieldRef Name="LinkFilenameNoMenu"/>
</ViewFields>

MS Team Form- Create new List item, create or update folder with file uploaded in the form

This Power Automate will trigger on when a new Team Form in SharePoint is submitted

  • It will create a new item in a SharePoint list
  •  It will check a SharePoint document library is a folder exists, if it does not exist, the flow will create a new folder and place uploaded file in the folder.
  •  If the folder does exist, it will place the uploaded file into the folder.

1. When a new response is submitted

Power Automate- Power BI- Error with Query Text- 400 not found

So this kept happening to me so I was messing around and this is how I fixed it.

I published my Power BI report so the Power Automate connector should have found it.

I had to find one more setting in my Power BI environment and it worked.

In Fabric (Power BI Online), on the left sidebar clink on "Browse".

Select your environment

Find your Report's "Semantic model" and click the three dots "..." to find more options.

Select "Settings"

Under Data source credentials, make sure there is a connections there. See below:

Power Automate- Power BI Run a query against a Dataset does not get all rows

Hello all,

Ran into this today.

Make sure you "Query text" is changed to the number of rows you would like to get back. I think it defaults to 500.  I changed mine to 100,000. See below

Date ranges Custom Columns in Power Query- Give a certain amount of payment if enrolled within two dates.

Table.AddColumn(#"Grouped rows", "ITSPaymentFinal", each if [HHIDEarliestEntry] <= #date(2024, 12, 15) then 225 else if [HHIDEarliestEntry] >= #date(2024, 12, 16) and [HHIDEarliestEntry] <= #date(2024, 12, 31)then 187.5 else if [HHIDEarliestEntry] >= #date(2025, 1, 1) and [HHIDEarliestEntry] <= #date(2025, 1, 15) then 150 else if [HHIDEarliestEntry] >= #date(2025, 1, 16) and [HHIDEarliestEntry] <= #date(2025, 1, 31) then 112.5 else if [HHIDEarliestEntry] >= #date(2025, 2, 1) and [HHIDEarliestEntry] <= #date(2025, 2, 15) then 75 else 0)

 

 

Text Inputs in Power Apps - Where did the .Text property go!? Search Galleries

If you are running into an issue that you inserted a "Text Input" box and all the sudden you do not have a "Text" property to write the search formula...

You inserted the wrong version of the Text Input.  There are two.

To get the .Text property for searching galleries, you need the CLASSIC Text Input. The other one is a Preview Text Input.

 

Sigh... 

Group By in Power Query Date Column Grab first date in list - DAT Script Dataverse

Here's the code for the entire grouping that put all the Students grouped by House Hold ID. However I needed the first date that the first student entered the school district.

 

Find Duplicate Items in SharePoint List- Power Automate

I think this could work for other Data Sources like dataverse as well.
Case Use: I used this to make sure duplicate appointment records did come over from the MS Bookings platform when I needed to do a large migration of the data  using power automate and the Microsoft Graph API.  Flow would bomb out and I think started again at the record it left off causing around 100 dups.

This flow helped me ID the duplicates and since there wasn't too many I went it and deleted in the list itself.  You could add a "Delete Item" action to this flow to do it for you.

 

 

Pages

Subscribe to Melinda Cozza - Power Platform and CMS Developer RSS