logo
down
shadow

Create multiple rows from a single row in PowerBI


Create multiple rows from a single row in PowerBI

Content Index :

Create multiple rows from a single row in PowerBI
Tag : powerbi , By : user183825
Date : November 25 2020, 07:22 PM

wish of those help I have a sales dataset where, in addition to (Sale#) and several foreign keys ID1, ID2, ID3, ID4 I have up to 3 Invoices (associated with a Sale#). E.g. Invoice#1, SaleAmount1, Date1, Invoice#2, SaleAmount2, Date2, Invoice#3, SaleAmount3, Date3; all as columns. , Yep, you just need to unpivot sets of columns.
Sales#  ID1  Date1     Date2     Date3
-----------------------------------------
Sale#1  123  1/1/2018  1/2/2018  1/3/2018
Sale#2  456  2/2/2018  3/3/2018  4/4/2018
Sales#  ID1 Attribute   Value
--------------------------------
Sale#1  123 Date1       1/1/2018
Sale#1  123 Date2       1/2/2018
Sale#1  123 Date3       1/3/2018
Sale#2  456 Date1       2/2/2018
Sale#2  456 Date2       3/3/2018
Sale#2  456 Date3       4/4/2018

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

Split multiple fields or columns of a single row and create multiple rows using Scala


Tag : scala , By : Julian Ivanov
Date : March 29 2020, 07:55 AM
To fix this issue You can convert DataFrame to Dataset[(String, String, String, String)] and flatMap:
import scala.util.Try

val df = Seq(
  ("A1", "B1", "C1", "D1"),
  ("A2", "B2,B3", "C2,C3", "D2,D3"),
  ("A1", "B4,B5,B6", "C4,C5,C6", "D4,D5,D6")
).toDF("x1", "x2", "x3", "x4")

// A simple sequence of expressions which allows us to flatten the results
val exprs = (0 until df.columns.size).map(i => $"value".getItem(i))

df.select($"x1", array($"x2", $"x3", $"x4")).as[(String, Seq[String])].flatMap {
  case (x1, xs) => 
    Try(xs.map(_.split(",")).transpose).map(_.map("x" +: _)).getOrElse(Seq())
}.toDF.select(exprs:_*)

// +--------+--------+--------+--------+
// |value[0]|value[1]|value[2]|value[3]|
// +--------+--------+--------+--------+
// |      A1|      B1|      C1|      D1|
// |      A2|      B2|      C2|      D2|
// |      A2|      B3|      C3|      D3|
// |      A1|      B4|      C4|      D4|
// |      A1|      B5|      C5|      D5|
// |      A1|      B6|      C6|      D6|
// +--------+--------+--------+--------+
val splitRow = udf((xs: Seq[String]) => 
   Try(xs.map(_.split(",")).transpose).toOption)

// Same as before but we exclude the first column
val exprs = (0 until df.columns.size - 1).map(i => $"xs".getItem(i))

df
  .withColumn("xs", explode(splitRow(array($"x2", $"x3", $"x4"))))
  .select($"x1" +: exprs: _*)

Create column to classify rows based on realted tables DAX PowerBI


Tag : powerbi , By : druta
Date : March 29 2020, 07:55 AM
I wish this help you Given that the relationships are inactive, I'd suggest using LOOKUPVALUE to match ID values on the other tables. You should be able to create a calculated column as follows:
Class = 
    VAR Size = LOOKUPVALUE('Size Table'[Size],
                   'Size Table'[Size code], 'Data Table'[Size code])
    VAR Color = LOOKUPVALUE('Color Table'[Color],
                   'Color Table'[Color code], 'Data Table'[Color code])
    RETURN SWITCH(TRUE(),
               (Size = "Medium") && (Color = "Green"), "A",
               (Size = "Large") && (Color = "Red"), "B", BLANK())
Class = SWITCH(TRUE(),
            (RELATED('Size Table'[Size]) = "Medium") &&
            (RELATED('Color Table'[Color]) = "Green"),
            "A",
            (RELATED('Size Table'[Size]) = "Large") &&
            (RELATED('Color Table'[Color]) = "Red"),
            "B", 
            BLANK())
Class = 
    VAR SizeColor = RELATED('Size Table'[Size]) & " " & RELATED('Color Table'[Color])
    RETURN SWITCH(TRUE(),
               SizeColor = "Medium Green", "A",
               SizeColor = "Large Red", "B",
               BLANK())

PowerBI Removing top rows by applied steps in PowerBI skips rows of multiple files


Tag : powerbi , By : THD
Date : March 29 2020, 07:55 AM
it helps some times This is probably caused by inconsistent Excel files. The most common issue that would cause the behavior you describe is hidden rows in Excel, which Power BI will read as data rows. But hey - it's Excel so the users could've done almost anything.
You can edit the Sample File query to point it at the file with issues which might give you more insight.

Split a single column into multiple rows in PowerBI?


Tag : powerbi , By : cynix
Date : January 08 2021, 10:52 AM
To fix the issue you can do 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.

Group multiple rows together in PowerBI


Tag : powerbi , By : Jim Davis
Date : March 29 2020, 07:55 AM
I wish this help you I have two columns: , I'd recommend a calculated Category column along these lines:
Category =
SWITCH (
    Table[PageName],
    "Page 1", "Category 1",
    "Page 2", "Category 3",
    "Page 3", "Category 2",
    "Page 4", "Category 2",
    "Page 5", "Category 1",
    "Page 6", "Category 2",
    "Page 7", "Category 3",
    "Page 8", "Category 3",
    "Page 9", "Category 1"
)
Category =
SWITCH (
    TRUE (),
    Table[PageName] IN { "Page 1", "Page 5", "Page 9" }, "Category 1",
    Table[PageName] IN { "Page 3", "Page 4", "Page 6" }, "Category 2",
    Table[PageName] IN { "Page 7", "Page 8", "Page 2" }, "Category 3"
)
Related Posts Related QUESTIONS :
shadow
Privacy Policy - Terms - Contact Us © scrbit.com