Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello
I have car resertvation data as in the first table, it is usual as the gantt char option.
but I want to convert to data as in the second table. means that I want to add for each day period value 1 to each booking.
I have to do to make a visual programmer (timeline) with matrix table, where only the background color of the value 1 and get visual.
Does anyone have any idea how to make such a transformation?
Solved! Go to Solution.
Hi @froxas ,
Sure, we can create a calculated table to meet your requirement:
NewTable =
ADDCOLUMNS (
CROSSJOIN (
DISTINCT (
SELECTCOLUMNS ( 'Table', "CarNumber", [Car number], "Project", [Project] )
),
CALENDAR ( MIN ( 'Table'[From] ), MAX ( 'Table'[Till] ) )
),
"Value",
VAR d = [Date]
VAR cn = [CarNumber]
VAR p = [Project]
RETURN
IF (
CALCULATE (
COUNTROWS ( 'Table' ),
'Table'[From] <= d,
'Table'[Till] >= d,
'PQTable'[Car number] = cn,
'PQTable'[Project] = p
) > 0,
1,
0
)
)
By the way, PBIX file as attached.
Best regards,
Hi @froxas ,
We can try to add five steps in the end of your query to meet your requirement:
such as the origin query like following:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8lDSUTIyMDLQNTAFIjjHTNfYAMgJMFSK1UFWBBWHcMx1DU0RigLg4ia6BobIxoI4AUZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Car number" = _t, From = _t, Till = _t, Project = _t]),
NameOfYourLastStep = Table.TransformColumnTypes(Source,{{"Car number", type text}, {"From", type date}, {"Till", type date}, {"Project", type text}})
in
NameOfYourLastStep
Then we change it to following:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8lDSUTIyMDLQNTAFIjjHTNfYAMgJMFSK1UFWBBWHcMx1DU0RigLg4ia6BobIxoI4AUZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Car number" = _t, From = _t, Till = _t, Project = _t]),
NameOfYourLastStep = Table.TransformColumnTypes(Source,{{"Car number", type text}, {"From", type date}, {"Till", type date}, {"Project", type text}}),
MinDate = Table.Min(NameOfYourLastStep,{"From"})[#"From"],
MaxDate = Table.Max(NameOfYourLastStep,{"Till"})[#"Till"],
CarNumberTable = Table.Distinct(Table.SelectColumns(NameOfYourLastStep,{"Car number","Project"})),
DateTable = Table.ExpandListColumn(Table.AddColumn(CarNumberTable,"Date",each List.Dates(MinDate,Duration.Days(MaxDate-MinDate)+1,#duration(1,0,0,0))), "Date"),
FinalTable = Table.AddColumn(DateTable,"Value",
each let car = [Car number], pro = [Project], d = [Date] in
if Table.RowCount(Table.SelectRows(NameOfYourLastStep,each [Car number]=car and [Project]=pro and [From] <= d and [Till] >= d))>0 then 1 else 0)
in
FinalTable
MinDate/MaxDate is the date range, then we get distinct rows for carnumber and project, add every date between MinDate and MaxDate to them, In FinalTable, we add a value to determine if this date exist in the origin table.
If it doesn't meet your requirement, Could you please show the exact expected result based on the tables that you have shared?
By the way, PBIX file as attached.
Best regards,
@v-lid-msft thanks
that's interesting solution, i will check it.
is there also method to make it with DAX creating table? maybe it's more easer, readable and maintainable solution?
Hi @froxas ,
Sure, we can create a calculated table to meet your requirement:
NewTable =
ADDCOLUMNS (
CROSSJOIN (
DISTINCT (
SELECTCOLUMNS ( 'Table', "CarNumber", [Car number], "Project", [Project] )
),
CALENDAR ( MIN ( 'Table'[From] ), MAX ( 'Table'[Till] ) )
),
"Value",
VAR d = [Date]
VAR cn = [CarNumber]
VAR p = [Project]
RETURN
IF (
CALCULATE (
COUNTROWS ( 'Table' ),
'Table'[From] <= d,
'Table'[Till] >= d,
'PQTable'[Car number] = cn,
'PQTable'[Project] = p
) > 0,
1,
0
)
)
By the way, PBIX file as attached.
Best regards,
@froxas how you are getting 0 or 1 value in value column?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2k that's how i need to transform my table. I need to create new table where i have all days and by every day value 1 if this reservatio is in that period.
User | Count |
---|---|
88 | |
84 | |
65 | |
62 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
82 | |
71 |