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 am looking for a way to cross reference two tables. The first table has a list of reporting dates. The second is a list of transactions. I would like to cross reference the report table to the transactions table that show what vendor is open each month. The Transactions has the Opening Date and may or may not have a Closing Date.
Example:
Report Dates:
April 1, 2020 to March 31, 2021
Transaction:
Vendor ID 0001 Open Date April 28, 2020 no closing date
Vendor ID 0002 Open Date June, 2020 closing date Sep 21, 2020
My table would be:
Vendor ID 0001 April 2020
Vendor ID 0001 May 2020
Vendor ID 0001 Jun 2020
Vendor ID 0002 Jun 2020
Vendor ID 0001 Jul 2020
Vendor ID 0002 Jul 2020
Vendor ID 0001 Aug 2020
Vendor ID 0002 Aug 2020
Vendor ID 0001 Sep 2020
Vendor ID 0002 Sep 2020
Vendor ID 0001 Oct 2020
Vendor ID 0001 Nov 2020
Vendor ID 0001 Dec 2020
Vendor ID 0001 Jan 2021
Vendor ID 0001 Feb 2021
Vendor ID 0001 Mar 2021
I am looking for how to do create this month by month table with Power Query in Power BI
Solved! Go to Solution.
Hi,
Try this. Please note that Parameters are used for Report Start/End.
This probably should be converted to function, but...
Just Replace Source with your table/previous step and Column1 with your column name.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkvNS8kvUvB0UTAwMDBU8C9IzVNwSSxJVXAsKMrMUTCy0FEwMjAyUMjLV0jOyS/OzEtXSAFKK8XqoOk1QtLrVZqXCtF3aAGKNoXg1AIFI0OInFJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), {"Column1"}),
Start = Table.AddColumn(Source, "Start", each if Text.Contains([Column1],"no closing date")
then Text.Combine(List.Range(Text.Split([Column1]," "), 5, 3)," ") else Text.Combine(List.Range(Text.Split([Column1]," "), 5, 2)," ")),
End = Table.AddColumn(Start, "End", each if Text.Contains([Column1],"no closing date")
then ReportEnd else Text.Combine(List.Range(Text.Split([Column1]," "), 9, 3)," ")),
Dates = Table.TransformColumnTypes(End,{{"Start", type date}, {"End", type date}}),
StartDate = Table.AddColumn(Dates, "DateStart", each List.Max({[Start],ReportStart})),
EndDate = Table.AddColumn(StartDate, "DateEnd", each List.Min({[End],ReportEnd})),
#"Removed Columns" = Table.RemoveColumns(EndDate,{"Start", "End"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"DateStart", type date}, {"DateEnd", type date}}),
MonthsRange = Table.AddColumn(#"Changed Type", "MonthsRange", each (12 * (Date.Year([DateEnd]) - Date.Year([DateStart])))
+ (Date.Month([DateEnd]) - Date.Month([DateStart]))
+ 1),
List = Table.AddColumn(MonthsRange, "MonthsList", each List.Numbers(1, [MonthsRange])),
Expanded = Table.ExpandListColumn(List, "MonthsList"),
Months = Table.AddColumn(Expanded, "OpenMonths", each Text.Combine(
{Text.Start(Date.MonthName(
Date.AddMonths(
[DateEnd],0 - [MonthsRange] + [MonthsList])),3), Number.ToText(Date.Year(Date.AddMonths([DateEnd],0 - [MonthsRange] + [MonthsList])))}, " ")),
Removed = Table.SelectColumns(Months,{"Column1", "OpenMonths"}),
Vendor = Table.AddColumn(Removed, "Vendor", each Text.Combine(List.Range(Text.Split([Column1]," "), 0, 3)," ")),
Removed2 = Table.SelectColumns(Vendor,{"Vendor", "OpenMonths"}),
#"Grouped Rows" = Table.Group(Removed2, {"OpenMonths"}, {{"GR", each _, type table [Vendor=text, OpenMonths=text]}}),
#"Expanded GR" = Table.ExpandTableColumn(#"Grouped Rows", "GR", {"Vendor"}, {"Vendor"}),
FINAL = Table.CombineColumns(#"Expanded GR",{"Vendor", "OpenMonths"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Result")
in
FINAL
I hope you have created parameters as suggested previously.
If you did, , right click on ReportEnd parameter, AdvancedEditor and completely replace existing code with:
let
Source = Date.From(Date.AddDays(Date.StartOfMonth(DateTime.LocalNow()), -1))
in
Source
In ReportStart parameter enter whichever date for desired static date, as initial value, and it should work...
Thank you for your answer. As a result I have learn much about viewing the Source using the advance editor.
I am new so I am going to as this simple question.
The source statement I am going to use an excel spread sheet. I have 16 columns of data that I am using in my month by month data set. These are my columns:
I should not have to worry to much about the splitting of the table list items as they already are defined. Am I correct on that assumption?
Thank you Everyone for your help.
Thank you for your answer. As a result I have learn much about viewing the Source using the advance editor.
I am new so I am going to as this simple question.
The source statement I am going to use an excel spread sheet. I have 16 columns of data that I am using in my month by month data set. These are my columns:
I should not have to worry to much about the splitting of the table list items as they already are defined. Am I correct on that assumption?
Hi,
Try this. Please note that Parameters are used for Report Start/End.
This probably should be converted to function, but...
Just Replace Source with your table/previous step and Column1 with your column name.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkvNS8kvUvB0UTAwMDBU8C9IzVNwSSxJVXAsKMrMUTCy0FEwMjAyUMjLV0jOyS/OzEtXSAFKK8XqoOk1QtLrVZqXCtF3aAGKNoXg1AIFI0OInFJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), {"Column1"}),
Start = Table.AddColumn(Source, "Start", each if Text.Contains([Column1],"no closing date")
then Text.Combine(List.Range(Text.Split([Column1]," "), 5, 3)," ") else Text.Combine(List.Range(Text.Split([Column1]," "), 5, 2)," ")),
End = Table.AddColumn(Start, "End", each if Text.Contains([Column1],"no closing date")
then ReportEnd else Text.Combine(List.Range(Text.Split([Column1]," "), 9, 3)," ")),
Dates = Table.TransformColumnTypes(End,{{"Start", type date}, {"End", type date}}),
StartDate = Table.AddColumn(Dates, "DateStart", each List.Max({[Start],ReportStart})),
EndDate = Table.AddColumn(StartDate, "DateEnd", each List.Min({[End],ReportEnd})),
#"Removed Columns" = Table.RemoveColumns(EndDate,{"Start", "End"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"DateStart", type date}, {"DateEnd", type date}}),
MonthsRange = Table.AddColumn(#"Changed Type", "MonthsRange", each (12 * (Date.Year([DateEnd]) - Date.Year([DateStart])))
+ (Date.Month([DateEnd]) - Date.Month([DateStart]))
+ 1),
List = Table.AddColumn(MonthsRange, "MonthsList", each List.Numbers(1, [MonthsRange])),
Expanded = Table.ExpandListColumn(List, "MonthsList"),
Months = Table.AddColumn(Expanded, "OpenMonths", each Text.Combine(
{Text.Start(Date.MonthName(
Date.AddMonths(
[DateEnd],0 - [MonthsRange] + [MonthsList])),3), Number.ToText(Date.Year(Date.AddMonths([DateEnd],0 - [MonthsRange] + [MonthsList])))}, " ")),
Removed = Table.SelectColumns(Months,{"Column1", "OpenMonths"}),
Vendor = Table.AddColumn(Removed, "Vendor", each Text.Combine(List.Range(Text.Split([Column1]," "), 0, 3)," ")),
Removed2 = Table.SelectColumns(Vendor,{"Vendor", "OpenMonths"}),
#"Grouped Rows" = Table.Group(Removed2, {"OpenMonths"}, {{"GR", each _, type table [Vendor=text, OpenMonths=text]}}),
#"Expanded GR" = Table.ExpandTableColumn(#"Grouped Rows", "GR", {"Vendor"}, {"Vendor"}),
FINAL = Table.CombineColumns(#"Expanded GR",{"Vendor", "OpenMonths"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Result")
in
FINAL
Can the parameter be programmed? For example I have a statice start date. My end date is always the end of the previous month from the current date.
Also I am new to Power BI. Is there any reference material for doing source coding like you have presented?
I hope you have created parameters as suggested previously.
If you did, , right click on ReportEnd parameter, AdvancedEditor and completely replace existing code with:
let
Source = Date.From(Date.AddDays(Date.StartOfMonth(DateTime.LocalNow()), -1))
in
Source
In ReportStart parameter enter whichever date for desired static date, as initial value, and it should work...
Thank You.
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.