Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a table like this and this is a very small sample:
Company | Period |
XYZ_DE | 202201 |
XYZ_NV | 202202 |
XYZ_US | 202203 |
XYZ_CN | 202204 |
ABC_DE | 202201 |
ABC_NV | 202202 |
ABC_US | 202203 |
ABC_CN | 202204 |
Basically in this case, since there are 8 companies with 4 months, I need a table with row count:
(#of days in Jan + #of days in Feb + #of days in Mar + #of days in Apr) * #of companies
Solved! Go to Solution.
Hi @cho ,
To convert your table into a basic scalar output, you can follow these steps:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WioiMindxVdJRMjIwMjIwVIrVgYj5hcHEjOBiocEwMWO4mLMfTMwELObo5IxhHkgM3TyQGLp5IDEU82IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Company = _t, Period = _t]),
addPeriodStart =
Table.AddColumn(Source, "periodStart", each
#date(
Number.From(Text.Start([Period], 4)),
Number.From(Text.End([Period], 2)),
01
)
),
addDaysInMonth = Table.AddColumn(addPeriodStart, "daysInMonth", each Date.DaysInMonth([periodStart])),
addCustomCalc = List.Sum(addDaysInMonth[daysInMonth]) / Table.RowCount(addDaysInMonth)
in
addCustomCalc
It outputs a scalar value of 30.
To actually expand your table into dated rows, you can follow these steps:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WioiMindxVdJRMjIwMjIwVIrVgYj5hcHEjOBiocEwMWO4mLMfTMwELObo5IxhHkgM3TyQGLp5IDEU82IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Company = _t, Period = _t]),
addPeriodStart =
Table.AddColumn(Source, "periodStart", each
#date(
Number.From(Text.Start([Period], 4)),
Number.From(Text.End([Period], 2)),
01
)
),
addListGen = Table.AddColumn(addPeriodStart, "listGen", each List.Transform(
{Number.From([periodStart])..Number.From(Date.EndOfMonth([periodStart]))},
each Date.From(_)
)),
exapndListGen = Table.ExpandListColumn(addListGen, "listGen")
in
exapndListGen
Which outputs this:
Pete
Proud to be a Datanaut!
Source is your original table
NewStep= let a=List.Distinct(Source[Company]),b=List.Sort(List.Distinct(Source[Period])),c=List.Sum(List.Transform(b,each Date.DaysInMonth(Date.From(_&"01")))) in #table({"Company","Date"},List.TransformMany(a,each List.Dates(Date.From(b{0}&"01"),c,Duration.From(1)),(x,y)=>{x,y}))
this gives you a table with 960 rows
Hi @cho ,
To convert your table into a basic scalar output, you can follow these steps:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WioiMindxVdJRMjIwMjIwVIrVgYj5hcHEjOBiocEwMWO4mLMfTMwELObo5IxhHkgM3TyQGLp5IDEU82IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Company = _t, Period = _t]),
addPeriodStart =
Table.AddColumn(Source, "periodStart", each
#date(
Number.From(Text.Start([Period], 4)),
Number.From(Text.End([Period], 2)),
01
)
),
addDaysInMonth = Table.AddColumn(addPeriodStart, "daysInMonth", each Date.DaysInMonth([periodStart])),
addCustomCalc = List.Sum(addDaysInMonth[daysInMonth]) / Table.RowCount(addDaysInMonth)
in
addCustomCalc
It outputs a scalar value of 30.
To actually expand your table into dated rows, you can follow these steps:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WioiMindxVdJRMjIwMjIwVIrVgYj5hcHEjOBiocEwMWO4mLMfTMwELObo5IxhHkgM3TyQGLp5IDEU82IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Company = _t, Period = _t]),
addPeriodStart =
Table.AddColumn(Source, "periodStart", each
#date(
Number.From(Text.Start([Period], 4)),
Number.From(Text.End([Period], 2)),
01
)
),
addListGen = Table.AddColumn(addPeriodStart, "listGen", each List.Transform(
{Number.From([periodStart])..Number.From(Date.EndOfMonth([periodStart]))},
each Date.From(_)
)),
exapndListGen = Table.ExpandListColumn(addListGen, "listGen")
in
exapndListGen
Which outputs this:
Pete
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.