cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Promoting a single row

I am pulling several poorly formatted Excel files into PowerBi, these are pulled directly from a a machine so the formatting must remain the same. 

 

The table looks like the below: 

Start TimeEnd Time  Duration
14:00:0015:00:00  01:00:00
  Product Water 
  Amount added 100 

 

I need to create a conditional column for the start and end time of water so I can see it on a gantt chart or similar. Since they are not on the same row, I cannot do this.

 

I know how to do this via indexes, however since there are around 50 different examples of this, I don't think doing this is feasible. Are there any other methods I could use to achieve the result I want? 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
New Member

Hi @ml2000 , please paste this query in your advanced editor & check if it works according to your requirement.

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSxMjAAIiUdJUNTOFMBig0MoUKxOtFwwYCi/JTS5BIQMzyxJLUILIyswDE3vzSvRCExJSU1BcQ3BJupFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Time" = _t, #"End Time" = _t, #" " = _t, #" .1" = _t, Duration = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Time", type time}, {"End Time", type time}, {" ", type text}, {" .1", type text}, {"Duration", type time}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[#" "]), " ", " .1"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{" "}),
#"Filled Down" = Table.FillDown(#"Removed Columns",{"Duration"}),
#"Filled Up1" = Table.FillUp(#"Filled Down",{"Duration", "End Time", "Start Time"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Up1", each ([#"Product "] = "Water"))
in
#"Filtered Rows"

View solution in original post

5 REPLIES 5
Highlighted
Super User IV
Super User IV

@ml2000 , can provide some kind of output you are looking for.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
New Member

Hi @ml2000 , please paste this query in your advanced editor & check if it works according to your requirement.

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSxMjAAIiUdJUNTOFMBig0MoUKxOtFwwYCi/JTS5BIQMzyxJLUILIyswDE3vzSvRCExJSU1BcQ3BJupFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Time" = _t, #"End Time" = _t, #" " = _t, #" .1" = _t, Duration = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Time", type time}, {"End Time", type time}, {" ", type text}, {" .1", type text}, {"Duration", type time}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[#" "]), " ", " .1"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{" "}),
#"Filled Down" = Table.FillDown(#"Removed Columns",{"Duration"}),
#"Filled Up1" = Table.FillUp(#"Filled Down",{"Duration", "End Time", "Start Time"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Up1", each ([#"Product "] = "Water"))
in
#"Filtered Rows"

View solution in original post

Highlighted

Thanks for your reply, ideally I would like the end result to look like this:

 

Start TimeEnd TimeProductAmount AddedDuration
14:00:0015:00:00Water10001:00:00

 

However if it looked like this ( this is what I was going for with the conditional columns), this would also work.

Start TimeEnd TimeWater Start TimeWater End TimeDurationWater Amount Added
14:00:0015:00:0014:00:0015:00:001:00:00100
Highlighted

This worked however I have already pivotted the columns earlier so I cannot do it again. 

Highlighted

Hi @ml2000 ,


Why can't you pivot the table again?

Can you share more information like Query or Pbix?

 

Best Regards,

Jay

 

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors