create 2 rows from 1 row - NOT splitting a column

[scotts1work]

How can I achieve the following in powerbi.

Let's say that my original data table (which I am intaking from an Excel file) has four fields and two rows of data, like:

Field 1	Field 2	Field 3	Field 4
ABC	3-Mar-2018	7-Mar-2018	abc
XYZ	1-Feb-2018	9-Jul-2018	xyz

Before I create my PowerBI visualization, I want to manipulate the data so that instead of having two data rows, I have four data rows like the following, where each of the original rows is used to derive two manipulated copies of that row :

Field 1	Field 2	Field 3	Field 4	Field 5	Field 6	Field 7
ABC	3-Mar-2018	7-Mar-2018	abc	3-Mar-2018	1.1	ABC-1
ABC	3-Mar-2018	7-Mar-2018	abc	7-Mar-2018	1.2	ABC-2
XYZ	1-Feb-2018	9-Jul-2018	xyz	1-Feb-2018	2.1	XYZ-1
XYZ	1-Feb-2018	9-Jul-2018	xyz	9-Jul-2018	2.2	XYZ-2

That is, for each data row in the first table, there is to be two data rows in the second table. There is a 1:2 ratio. All four of the original fields are copied verbatim to the second table. But there are three new fields in the second table.

Field 5 in the second table always equals Field 2 (in the first row out of each pair of rows) or Field 3 (in the second row).

Field 6 in the second table is a decimal number, where the whole number portion refers to the row number for the corresponding row from the original table, followed by either .1 or .2, where the decimal portion serves as an indication of whether Field 5 is based on field 2 or field 3 (in other words .1=this row is the first row in the pair; .2= second row in pair).

Field 7 = field 1 concatenated with either -1 or -2 as an indication of whether Field 5 is based on field 2 or field 3.

(If we can get Field 6 to work properly, then I don't need Field 7. Field 7 is just a backup approach in case field 6 is too complicated to calculate.)

The reason for this is that I want to draw a graphic where the PowerBI visualization creates one graphical element for each data row. But my source data contains two separate pieces of information in each row, and I need each piece of information to be a graphical element in the visualization. So by breaking these pieces of information into two separate rows, I can get all the pieces of information into the visualization.

Note that all the information is already in separate fields in my source data (one value per field). I found other posts on this forum on how to expand a single row into multiple rows where one source field contains multiple delimited values... but that is different than my situation.

[Anonymous · Accepted solution]

Hi @scotts1work,

You can refer to below query formula to group your records.

Result:

Full query formula:

Spoiler

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRMtY31jcyMLQAM81hzMSkZKVYnWiliMgoIM9I3xAmYa5vCWNWVFYpxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Field 1" = _t, #"Field 2" = _t, #"Field 3" = _t, #"Field 4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Field 1", type text}, {"Field 2", type date}, {"Field 3", type date}, {"Field 4", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Fileds", each Table.AddIndexColumn(Table.FromList(Text.Split(Text.From(_[Field 2])&","&Text.From(_[Field 3]),","), Splitter.SplitByNothing(),{"Date"}, null, ExtraValues.Error), "Index", 1, 1)),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 1, 1),
    #"Expanded Fileds" = Table.ExpandTableColumn(#"Added Index", "Fileds", {"Date", "Index"}, {"Fileds.Date", "Fileds.Index"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Fileds", "Field 6", each Text.From([Index])&"."&Text.From([Fileds.Index])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Field 7", each Text.From([Field 1])&"-"&Text.From([Fileds.Index])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Fileds.Index", "Index"})
in
    #"Removed Columns"

Regards,

Xiaoxin Sheng

[scotts1work]

Thanks Xiaoxin.  I sent your solution to my team for them to give it a try.

A colleague also proposed to me the concept outlined below (untested), but Xiaoxin's solution has the advantage of being more compact.

Original Excel Table

Field 1	Field 2	Field 3	Field 4
ABC	3-Mar-2018	7-Mar-2018	abc
XYZ	1-Feb-2018	9-Jul-2018	xyz

Intake Original Excel Table into new PowerBI table 1. In this step, create an autonumber field.

PowerBI table 1

Field 1	Field 2	Field 3	Field 4	Autonumber Field
ABC	3-Mar-2018	7-Mar-2018	abc	1
XYZ	1-Feb-2018	9-Jul-2018	xyz	2

Copy PowerBI table 1 into new PowerBI table 2.  In this step, create new field 5 [set equal to (table 1 Autonumber Field+0.1].  Create new field 6 [set equal to Field 2]

PowerBI table 2

Field 1	Field 2	Field 3	Field 4	Field 5	Field 6
ABC	3-Mar-2018	7-Mar-2018	abc	1.1	3-Mar-2018
XYZ	1-Feb-2018	9-Jul-2018	xyz	2.1	1-Feb-2018

Copy PowerBI table 1 into new PowerBI table 3.  In this step, create new field 5 [set equal to (table 1 Autonumber Field+0.2].  Create new field 6 [set equal to Field 3]

PowerBI table 3

Field 1	Field 2	Field 3	Field 4	Field 5	Field 6
ABC	3-Mar-2018	7-Mar-2018	abc	1.2	7-Mar-2018
XYZ	1-Feb-2018	9-Jul-2018	xyz	2.2	9-Jul-2018

Merge together PowerBI table 2 and PowerBI table 3 into new PowerBI table 4.  In this step, sort PowerBI table 4 by Field 5.

PowerBI table 4

Field 1	Field 2	Field 3	Field 4	Field 5	Field 6
ABC	3-Mar-2018	7-Mar-2018	abc	1.1	3-Mar-2018
ABC	3-Mar-2018	7-Mar-2018	abc	1.2	7-Mar-2018
XYZ	1-Feb-2018	9-Jul-2018	xyz	2.1	1-Feb-2018
XYZ	1-Feb-2018	9-Jul-2018	xyz	2.2	9-Jul-2018