Forum Discussion
Power BI Tables
I have a table as per below format:
| Incident | Applications | Downtime Hours |
| 001 | X Y | 5 |
| 002 | A B C | 2 |
| 003 | A1 A2 A3 A4 | 3 |
How to conver to below in Power BI? With each row for the data in the column:
| Incident | Applications | Downtime Hours |
| 001 | X | 5 |
| 001 | Y | 5 |
| 002 | A | 2 |
| 002 | B | 2 |
| 002 | C | 2 |
| 003 | A1 | 3 |
| 003 | A2 | 3 |
| 003 | A3 | 3 |
| 003 | A4 | 3 |
Hi,
This one works
let Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Incident", Int64.Type}, {"Applications", type text}, {"Downtime Hours", Int64.Type}}), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Applications", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Applications") in #"Split Column by Delimiter"
14 Replies
before :
after :
if this is what you want,
then you can choose the column that you want to split,
and split to rows instead of columns in the power query
hope it helps
Good one... However after the last delimeter it add an extra row
if it is a blank row, you can in power query , remove blank rows
Bunny25 can you try this
let Source = {[colA=1,colB="A B"],[colA=2,colB="X Y Z"]}, #"Expanded Column1" = Table.ExpandRecordColumn(Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Column1", {"colA", "colB"}, {"colA", "colB"}), #"Added Custom" = Table.AddColumn(#"Expanded Column1", "Custom", each Text.Split([colB]," ")), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"colB"}) in #"Removed Columns"Hi,
This one works
let Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Incident", Int64.Type}, {"Applications", type text}, {"Downtime Hours", Int64.Type}}), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Applications", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Applications") in #"Split Column by Delimiter"to know how to do this watch my video
https://1drv.ms/v/s!AiUZ0Ws7G26RjEeSyXM5Oas0EBQ4?e=wzE5lJ
or
https://www.youtube.com/watch?v=uxhKTZFigYI&ab_channel=TheBIWizzKid
Not good
ok watch this video
Open the table on power query and apply fill up or fill down (as per requirement).
Not good
Hello,
in power query , there is a feature called : Fill
initial data :
after using fill
NB : you need to select the column and then apply the fill option
