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 need a power query custom column formula like the one shown in excel
Can somebody help? :=)
Its should count if its the same mRID and month
Solved! Go to Solution.
2. Name your Query i.e. MyData (without spaces and special characters)
3. Create blank query, open advanced editor, select whole code and replace it with the code I provided above.
4. Close Advanced Editor
5. Select Source Step from right panel Query Settings
6. Delete whole code of Source step (as you can see selected in the picture in Note below in my signature) and write there = MyData (which is your table reference)
Makes sence, perfect! Thank you so much! 😄
I changed the code a tiny bit (mRID - to text.type)
let
Source = MyData,
ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Budget", Currency.Type}, {"mRID", Text.Type}}, "sk-SK"),
GroupedRows = Table.Group(ChangedType, {"Date", "mRID"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table}}),
ExpandedAll = Table.ExpandTableColumn(GroupedRows, "All", {"Budget"}, {"Budget"})
in
ExpandedAll
Hi @AndreasMEG
Result
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyNjJR0lUwMDAyBlaGRsrBSrQ4SkEVE6jeCSJpiShnBJU6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Budget = _t, mRID = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Budget", Currency.Type}, {"mRID", Int64.Type}}, "sk-SK"),
GroupedRows = Table.Group(ChangedType, {"Date", "mRID"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table}}),
ExpandedAll = Table.ExpandTableColumn(GroupedRows, "All", {"Budget"}, {"Budget"})
in
ExpandedAll
I already have a table in power bi
Sources are from API's
Need a custom column formula which is simular to the excel formula
Hope it makes sence, and thanks! 🙂
I dont understand step 4, the code pasted is from step 3.)
let
Source = Json.Document(Web.Contents("https://rest.datapoint.dk")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"GSRN", "ProductionMeterPoint", "ConsumptionMeterPoint", "TurbineID", "TurbineName", "LegalOwner", "PortFolio", "CapacityMW", "BalanceResponsible", "DWI", "Ownership_Percent", "PriceCode", "FacilityAddress", "AcquisitionDate", "MA_Deal", "Land", "LeaseDKK", "LeasePercent", "LeaseRenewalYear"}, {"GSRN", "ProductionMeterPoint", "ConsumptionMeterPoint", "TurbineID", "TurbineName", "LegalOwner", "PortFolio", "CapacityMW", "BalanceResponsible", "DWI", "Ownership_Percent", "PriceCode", "FacilityAddress", "AcquisitionDate", "MA_Deal", "Land", "LeaseDKK", "LeasePercent", "LeaseRenewalYear"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"GSRN", type text}, {"ProductionMeterPoint", type text}, {"ConsumptionMeterPoint", type text}, {"TurbineID", Int64.Type}, {"TurbineName", type text}, {"LegalOwner", type text}, {"PortFolio", type text}, {"CapacityMW", type number}, {"BalanceResponsible", type text}, {"DWI", Int64.Type}, {"Ownership_Percent", Int64.Type}, {"PriceCode", type text}, {"FacilityAddress", type text}, {"AcquisitionDate", type datetimezone}, {"MA_Deal", type text}, {"Land", type text}, {"LeaseDKK", type text}, {"LeasePercent", Percentage.Type}, {"LeaseRenewalYear", type text}}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"ProductionMeterPoint", null}}),
#"Replaced Errors1" = Table.ReplaceErrorValues(#"Replaced Errors", {{"TurbineID", null}}),
#"Merged Queries" = Table.NestedJoin(#"Replaced Errors1", {"ProductionMeterPoint"}, Afregnet, {"mRID"}, "Afregnet", JoinKind.LeftOuter),
#"Expanded Afregnet" = Table.ExpandTableColumn(#"Merged Queries", "Afregnet", {"Date", "Afregnet produktion"}, {"Afregnet.Date", "Afregnet.Afregnet produktion"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Afregnet",{{"Afregnet.Date", "Date"}, {"Afregnet.Afregnet produktion", "Afregnet produktion"}})
in
#"Renamed Columns"
2. Name your Query i.e. MyData (without spaces and special characters)
3. Create blank query, open advanced editor, select whole code and replace it with the code I provided above.
4. Close Advanced Editor
5. Select Source Step from right panel Query Settings
6. Delete whole code of Source step (as you can see selected in the picture in Note below in my signature) and write there = MyData (which is your table reference)
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.