cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Flyckten Frequent Visitor
Frequent Visitor

Merge alternative columns dynamically used in a function

Hi,

 

So I have this data in multiple files in a folder. The data is formated in the following way:

First column: Date and time

Second column: Measured value of series 1

Third column: Status of measured value  series 1

Forth column: Measure value of series 2

Fifth column: Status value of series 2

 

Sample:

TimestampValue 1Status 1Value 2Status 2   ...Value NStatus N
2017-12-01 00:004Calculated3  1 
2017-12-01 01:003Calculated2Estimated 6 
2017-12-01 02:002Calculated6  8Calculated
2017-12-01 03:000Calculated8  5Calculated

 

 

The amount of columns is dependent on how many series is in that file. I would like to make a function to clean and make the data neat for all the files in the folder. I do this by merging the column Measured value and Status and then splitting it and normalizing it. But since the data has different amount of columns cross the files I'm having a hard time creating a function for this. Anybody have any good suggestions to create a function to merge every other column except the first one, independent on number of columns? 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Merge alternative columns dynamically used in a function

Hi @Flyckten 

If all files in a folder have the same format, you could Get Data from Folder with Power BI,

Then Click Edit, expand the "Content"

7.png

 

Select "Source.Name" and "Timestamp" columns and unpivot other columns

8.png

Rename the "Value" column to "combine Status"

9.png

Spilt the "Attribute" column 

10.png

rename "Attribute.2" to "Series"

Select ""Attribute.2" and pivot columns

11.png

 

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 REPLIES 2
Community Support Team
Community Support Team

Re: Merge alternative columns dynamically used in a function

Hi @Flyckten 

If all files in a folder have the same format, you could Get Data from Folder with Power BI,

Then Click Edit, expand the "Content"

7.png

 

Select "Source.Name" and "Timestamp" columns and unpivot other columns

8.png

Rename the "Value" column to "combine Status"

9.png

Spilt the "Attribute" column 

10.png

rename "Attribute.2" to "Series"

Select ""Attribute.2" and pivot columns

11.png

 

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User
Super User

Re: Merge alternative columns dynamically used in a function

Hi @Flyckten,

you've already get a solution from @v-juanli-msft.

But an alternative could be using functions like Table.ColumnNames or Record.FieldNames. You get a list of all columns of a table or a record which you can filter with List.Select. And then you can do your magic. It expects more programming skills in M.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 378 members 3,453 guests
Please welcome our newest community members: