Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
rig94
New Member

Splitting a column with multiple data

Hello,

I have some data i am currently working with that has the user and date combined together in one column the dates and user ids will change was wondering if there is a way to dyanmically extract some of this data. If not if i set it up to pick up the month and year is there a way to dynamically pick up the user.

 

The below is some dummy data that is similar to the original data im using, thanks.

 

rig94_0-1701947739147.png

 

1 ACCEPTED SOLUTION
kleigh
Resolver II
Resolver II

Create a calc column, if(Text.StartsWith([Column1], "User") then [Column1] else null

Then use fill down to fill the null values:
Fill values in a column - Power Query | Microsoft Learn

Then delete the rows with null counts.

View solution in original post

2 REPLIES 2
rig94
New Member

Hello,

I actually found a way to do it similar as you put above but using contains then doing a few or options for the the year upto 2026 to make it more dynamic with the user id side as that was a demo and it wont always be user but a client name that can change. I then used downfill down on the client name left and filter blank rows out.

Example steps for anyone else attempting:
#"Added Conditional Column" = Table.AddColumn(#"Removed Top Rows1", "Custom", each if Text.Contains([Column1], "2022") or Text.Contains([Column1], "2023") or Text.Contains([Column1], "2024") or Text.Contains([Column1], "2025") or Text.Contains([Column1], "2026") then null else [Column1]),
#"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}),

 

 

kleigh
Resolver II
Resolver II

Create a calc column, if(Text.StartsWith([Column1], "User") then [Column1] else null

Then use fill down to fill the null values:
Fill values in a column - Power Query | Microsoft Learn

Then delete the rows with null counts.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors