Weekly data converted into daily data based on phased targets

[Anonymous]

I have a dataset that updates weekly, in power query editor and I have a table that I want to use with that data to tell me if the orders fulfilled are performing against target. I can't see a way of doing this without it repeating the data for each day (so i end up with 5x the orders for Mon-Fri which isn't correct) but is it possible to base the split of the data on the table that gives me a target for orders fulfilled which has a daily target (see second table below)?

I have a date table too, so have the start date of the week etc. but if i merge this with my dataset, it just maps my 'Mon-Fri' from the target table against every date. I feel like i'm missing something obvious here; is there a way to add in dates and days without repeating the data? At this point, i'd accept it splitting the orders by 20% for each date because then maybe I could fix it later on??

DATASET	ORDERS PLACED	ORDERS FULFILLED	ORDERS PLACED	ORDERS FULFILLED	ORDERS PLACED	ORDERS FULFILLED
FISCAL WEEK	1	1	2	2	3	3
DATE	27/02/2023	27/02/2023	06/03/2023	06/03/2023	13/03/2023	13/03/2023
PLANT A	20	20	20	20	20	20
PLANT B	20	15	10	20	20	15
PLANT C	30	25	20	30	30	25

TARGETS	MON	TUES	WED	THURS	FRI
PLANT A	0.5	0.67	0.83	0.94	0.99
PLANT B	0.5	0.75	0.9	0.99	1
PLANT C	0.3	0.6	0.8	0.99	1

[KNP]

Excel version of the Power BI file attached.

[Anonymous]

Hi, thank you for responding- I can't download the pbix as I don't have power bi on my work laptop and IT won't let me download it; I am using power query editor for excel files. The data I provided is dummy data and I have already unpivoted the actual data needed in my work, I have provided some of my code below, but I may be coming at it wrong and haven't explained myself well.

let
Source = Csv.Document(File.Contents("C:\Users\OneDrive - y\Documents\Consumption Week Project\BI Weekly Demand Extract.csv"),[Delimiter="=", Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.Csv]),
RefPoint = Source,
#"Added Index" = Table.AddIndexColumn(RefPoint, "Index", 1, 1, Int64.Type),
#"Filtered Rows" = Table.SelectRows(#"Added Index", each Text.StartsWith([Column1], "Fiscal year/period")),
Index = #"Filtered Rows"{0}[Index],
Custom1 = RefPoint,
#"Removed Top Rows" = Table.Skip(Custom1,Index-1),
#"Replaced Value" = Table.ReplaceValue(#"Removed Top Rows","""","",Replacer.ReplaceText,{"Column1"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "Column1", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true]),
#"Replaced Value1" = Table.ReplaceValue(#"Promoted Headers",",","",Replacer.ReplaceText,{",,"}),
#"Renamed Columns" = Table.RenameColumns(#"Replaced Value1",{{",,", "HL"}, {"", "Value Type"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"_3", "Column11"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Fiscal Week", each Date.StartOfWeek
(Date.AddWeeks
(#date(Number.FromText(Text.End([#"Fiscal year/period"],4)), 1, 1),
Number.FromText([Fiscal WW])-1), Day.Monday)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Fiscal Month", each #date(
Number.FromText(Text.End([#"Fiscal year/period"],4)),
Number.FromText(Text.Start([#"Fiscal year/period"],3)),
1)),
#"Filtered Rows1" = Table.SelectRows(#"Added Custom1", each true),
#"Added Custom2" = Table.AddColumn(#"Filtered Rows1", "Month", each Date.MonthName(
#date(2000,
Number.FromText(
Text.Start([#"Fiscal year/period"],3) ), 1)
,"MMMM")),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom2",{{"HL", type number}, {"Fiscal Week", type date}, {"Fiscal Month", type date}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[#"Value Type"]), "Value Type", "HL", List.Sum),
#"Replaced Value6" = Table.ReplaceValue(#"Pivoted Column","Impulse","Off Premise",Replacer.ReplaceText,{"Sales Office_1"}),
#"Merged Queries1" = Table.NestedJoin(#"Replaced Value6", {"Sales Office_1"}, SalesOffPhase, {"Sales Office Desc"}, "SalesOffPhase", JoinKind.LeftOuter),
#"Expanded SalesOffPhase" = Table.ExpandTableColumn(#"Merged Queries1", "SalesOffPhase", {"Phase Day", "Target Consumption"}, {"Phase Day", "Target Consumption"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded SalesOffPhase",{{"Plant", Int64.Type}}),

[KNP]

Firstly, you should avoid having your data in this layout and if this is how you get it, then I'd suggest unpivoting.

Unpivoting multiple rows like this can be complex, thankfully people like Bill Szysz and @ImkeF are in the community and have simplified it for us. 

(below code taken from the end of this post, https://www.thebiccountant.com/2017/06/19/unpivot-by-number-of-columns-and-rows-in-powerbi-and-power...)

let
    Source = (t as table, FirstNColumnsToKeep as number, FirstNRowsToSkip as number)=>


let
// ------------------------------------------------------------------------------------------------------------------------
// This part creates a fake, one row table with column names like in original table  ( first row (the only row) will be promoted later )

            Tab = Table.FirstN(t, FirstNRowsToSkip),
            Custom1 = Table.Transpose(Tab),
            ChangedType = Table.TransformColumnTypes(Custom1,List.Transform(Table.ColumnNames(Custom1), each {_, type text})),
            AllColumnNames = Table.ColumnNames(Tab),
            ConstColumnNames = List.FirstN(AllColumnNames,FirstNColumnsToKeep),
            ListOfTransposedColumnNames = Table.ColumnNames(ChangedType),

//             ------------------------------------
// The line below is not necessary if null is real null value and not "null" (like in Table1)


            #"Replaced Value" = Table.ReplaceValue(ChangedType,"null",null,Replacer.ReplaceValue, ListOfTransposedColumnNames),

//             -----------------------------------

            ReadyToMerge = Table.FillDown(#"Replaced Value", ListOfTransposedColumnNames),
            #"Merged Columns" = Table.CombineColumns(ReadyToMerge, ListOfTransposedColumnNames, Combiner.CombineTextByDelimiter("$_$", QuoteStyle.None),"MergedColumns"),
            #"Filtered Rows" = Table.Skip(#"Merged Columns", FirstNColumnsToKeep)[MergedColumns],
            AllNewNames = Table.FromRows({ConstColumnNames & #"Filtered Rows"}, AllColumnNames),
// -------------------------------------------------------------------------------------------------------------------------

// This part combines fake table (AllNewNames) and original table (t) without FirstNRowsToSkip and then it does a few (easy) necessary things

    Combine = Table.Combine( {AllNewNames, Table.Skip(t, FirstNRowsToSkip)}),
    #"Promoted Headers" = Table.PromoteHeaders(Combine, [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", ConstColumnNames, "Attrib", "Value"),
    ListOfSplitedColumnNames = List.Transform({1..FirstNRowsToSkip}, each "Attrib." & Text.From(_) ),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns","Attrib",Splitter.SplitTextByDelimiter("$_$", QuoteStyle.Csv),ListOfSplitedColumnNames),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter", List.Transform(ListOfSplitedColumnNames, each {_, type text}))
in
    #"Changed Type"

// Documentation ( I've stolen this part from You Imke;-))	
	, documentation = [
Documentation.Name =  " fxUnpivBill
", Documentation.Description = " Unpivots a table according to the number of columns and header rows passed on in the parameters
" , Documentation.LongDescription = " Unpivots a table according to the number of columns and header rows passed on in the parameters
", Documentation.Category = " Table functions
", Documentation.Source = " local
", Documentation.Author = " Bill Szysz
", Documentation.Examples = {[Description =  " 
" , Code = " Look at the code in Advanced Editor to read some notes
 I've stolen this part (Documentation part) from You Imke ;-)
 ", Result = " 
"]}] 
 in 
  Value.ReplaceType(Source, Value.ReplaceMetadata(Value.Type(Source), documentation))

If you create a blank query and copy and paste this into the advanced editor, you'll have an easy way to get your data in a better format.

See attached PBIX, if it helps you. Important to demote the headers first before running the table through the function.

I have made changes in the attached PBIX (see Output table) that is in line with what I think you're trying to achieve, but have a look and let me know.