CODEWITHSUNDEEP
powerbidax
harishmd
harishmd

Reputation: 21

Split a single column into multiple rows in PowerBI?

I'm trying to transform a single cell in a column into several rows. What technique or DAX should I use in PowerBI to achieve the result.

The table is details are given below,

    +------------------------------------------------+----------------+
    |                      Time                      |     Status     |
    +------------------------------------------------+----------------+
    | TimeStamp (2019-01-02, 2019-01-03, 2019-01-04) | (Yes, Yes, No) |
    +------------------------------------------------+----------------+

I wanted a output like this,

    +------------+----------+
    |    Time    |  Status  |
    +------------+----------+
    | 2019-01-02 | Yes      |
    | 2019-01-03 | Yes      |
    | 2019-01-04 | No       |
    +------------+----------+

I have tried several solution and not able to attain a conclusion on PowerBI.

Upvotes: 0

Views: 2389

Answers (2)

Aldert
Aldert

Reputation: 4323

The easiest is to use Power Query. Open the advanced editor and past the code below. You need to edit the part where you get the data:

let
    GetData = (test as record) => let
        textTimeStamp = Text.BetweenDelimiters(test[TimeStamp], "(", ")"),
        tableTimeStamp = Table.FromList(Function.Invoke(Splitter.SplitTextByDelimiter(","), {textTimeStamp}),null,{"TimeStamp"}),
        tableTimeStampIndex = Table.AddIndexColumn(tableTimeStamp, "Index"),

        textAnswer = Text.BetweenDelimiters(test[Answer], "(", ")"),
        tableAnswer = Table.FromList( Function.Invoke(Splitter.SplitTextByDelimiter(","), {textAnswer}),null,{"Answer"}),
        tableAnswerIndex = Table.AddIndexColumn(tableAnswer, "Index"),

        joinedTable = Table.Join(tableTimeStampIndex, "Index", tableAnswerIndex, "Index"),
        removeIndex = Table.RemoveColumns(joinedTable,{"Index"})
    in
        removeIndex,


    TimeSplit = let
    Source = Csv.Document(File.Contents("C:\Users\...\Documents\TimeSplit.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true])
in
    #"Promoted Headers",
    #"Invoked Custom Function" = Table.AddColumn(TimeSplit, "ToList", each GetData(_)),
    #"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",{"TimeStamp", "Answer"}),
    #"Expanded ToList" = Table.ExpandTableColumn(#"Removed Columns", "ToList", {"TimeStamp", "Answer"}, {"TimeStamp", "Answer"})
in
    #"Expanded ToList"

Upvotes: 1

Prem Chand
Prem Chand

Reputation: 126

I have used both powerquery and DAX to solve this problem

First I have created book1 to get the status column and split column by (,) using row delimited option instead of column delimited and added index to it. Then book2 table created to get only the Time column and split column by (,) using same row delimited option and added index to it.

Book 1: Powerquery

let Source = Csv.Document(File.Contents("C:\Users\PremChand\Desktop\stack\Book1.csv"),Delimiter=",", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), #"Replaced Value" = Table.ReplaceValue(#"Promoted Headers","TimeStamp","",Replacer.ReplaceText,{"Time"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","(","",Replacer.ReplaceText,{"Time", "Status"}), #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",")","",Replacer.ReplaceText,{"Time", "Status"}), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Replaced Value2", {{"Status", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Status"), #"Added Index" = Table.AddIndexColumn(#"Split Column by Delimiter", "Index", 0, 1), #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Time"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Index", "Index1"}}) in #"Renamed Columns"

Book 2: Powerquery

let Source = Csv.Document(File.Contents("C:\Users\PremChand\Desktop\stack\Book1.csv"),[Delimiter=",", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), #"Replaced Value" = Table.ReplaceValue(#"Promoted Headers","TimeStamp","",Replacer.ReplaceText,{"Time"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","(","",Replacer.ReplaceText,{"Time", "Status"}), #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",")","",Replacer.ReplaceText,{"Time", "Status"}), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Replaced Value2", {{"Time", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Time"), #"Added Index" = Table.AddIndexColumn(#"Split Column by Delimiter", "Index", 0, 1), #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Status"}) in #"Removed Columns"

Once book1 and book2 has created relationship has created for both Index column. Then the new output table has created using DAX to join book1 and book2.

Below DAX used to join two tables:

Output = NATURALLEFTOUTERJOIN(Book2,Book1)

The Output table consist of Time, Status and Index and Index1 column you can select only Time and status column to show in a table.

enter image description here

Upvotes: 1

Related Questions